WHEN EXTRACT( YEAR FROM yourdate ) < 1000 THEN '0' WHEN EXTRACT( YEAR FROM yourdate ) < 100 THEN '00' WHEN EXTRACT( YEAR FROM yourdate ) < 10 THEN '000' || LPAD( EXTRACT( MONTH FROM yourdate ), 4, '0' )īut neither of those are in the ANSI standard so: SELECT CASE Or LPAD: SELECT LPAD( EXTRACT( YEAR FROM yourdate ), 4, '0' ) || TO_CHAR( EXTRACT( MONTH FROM yourdate ), 'FM00' ) However, if the year is not 4-digits or the month is not 2-digits then you need to pad the values again, the simple solution is TO_CHAR: SELECT TO_CHAR( EXTRACT( YEAR FROM yourdate ), 'FM0000' ) Or, using an implicit cast SELECT EXTRACT( YEAR FROM yourdate ) || CAST( EXTRACT( MONTH FROM yourdate ) AS VARCHAR2(2) ) || TO_CHAR( EXTRACT( MONTH FROM yourdate ) )īut you were trying to avoid TO_CHAR so you could do: SELECT CAST( EXTRACT( YEAR FROM yourdate ) AS VARCHAR2(4) ) Then you need to convert the numbers to a string and concatenate the strings: SELECT TO_CHAR( EXTRACT( YEAR FROM yourdate ) ) However, you can get the year and month components using the EXTRACT function (which is in the ANSI standard): SELECT EXTRACT( YEAR FROM yourdate ), The simplest solution is to use the functions Oracle provides for that purpose: SELECT TO_CHAR( yourdate, 'YYYYMM' ) FROM yourtable There is no function specified in the ANSI SQL92 standard which formats DATETIME datatypes as a string. I am trying to standardize on ANSI SQL to the degree possible and avoid proprietary vendor nonstandard implementations.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |