SQL field add date change format to YYYYMMDD

Question

I have one field for date contains year and months. Field name new_TarikhSebenarTamatPengajian. Exmaple of data 199110 (Format:YYYYMM). I want to add date to set a new format require are (YYYYMMDD). For the available data Can I add date follow end of the month for that month. Example 199101 = 19910130 OR 199102 = 19910228 OR 199103 = 19910231

This code is not work for me

SELECT DMSTAG.dbo.TEMP_AKAUN_MARA.new_TarikhSebenarTamatPengajian CASE WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 01 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 02 THEN CONCAT(new_TarikhSebenarTamatPengajian,28) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 03 THEN CONCAT(new_TarikhSebenarTamatPengajian,31)
WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 04 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 05 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 06 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 07 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 08 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 09 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 10 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 11 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 12 THEN CONCAT(new_TarikhSebenarTamatPengajian,31)
END AS newdate FROM [dbo].[TEMP_AKAUN_MARA]

Those aren't date fields, those are text fields. You would be well advised to correct your schema and make date fields date fields. — Steve Wellens, Nov 11 '14 at 03:29

score 1 · Answer 1 · answered Nov 11 '14 at 04:33

Assuming your column name is DateWithOutMonth :

Select Convert(nvarchar(8), Dateadd(day, -1, Dateadd(month, 1, Convert(datetime, DateWithOutMonth + '01'))) , 112) 
from your table

Code explained :

first, add 01 to the end of your date value, so the value will always is the beginning of a month
convert the value to date time type
add 1 month into the newly converted value
minus one day from the new ly added value -> we have the end date of the month

Hardik Parmar · Answer 2 · 2014-11-11T02:54:02.157

0

You can simply do this to acheive your functionality.

I hope this will help you

    SELECT   
      CASE
        WHEN CRSE_DATE = '199101' THEN '19910130'
        WHEN CRSE_DATE = '199102' THEN '19910228'
        WHEN CRSE_DATE = '199103' THEN '19910231'
      END 
    FROM 
    table_name

edited Nov 11 '14 at 02:54

answered Nov 11 '14 at 02:47

Hardik Parmar

1,053
3
15
39

Edrich · Accepted Answer · 2014-11-11T04:27:18.613

0

Try this one:

SELECT id,crse_date,
    CASE 
        WHEN RIGHT(crse_date,2) = 01 THEN CONCAT(crse_date,31)
        WHEN RIGHT(crse_date,2) = 02 THEN CONCAT(crse_date,28)
        WHEN RIGHT(crse_date,2) = 03 THEN CONCAT(crse_date,31)   
        WHEN RIGHT(crse_date,2) = 04 THEN CONCAT(crse_date,30)       
    END AS newdate
 FROM yourtable


After you alter your table. Here is new query for your problem
Takenote : your field name is not advisable

SELECT new_TarikhSebenarTamatPengajian, 
    CASE
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 01 THEN CONCAT(new_TarikhSebenarTamatPengajian,31)
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 02 THEN CONCAT(new_TarikhSebenarTamatPengajian,28) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 03 THEN CONCAT(new_TarikhSebenarTamatPengajian,31)
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 04 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 05 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 06 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 07 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 08 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 09 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 10 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 11 THEN CONCAT(new_TarikhSebenarTamatPengajian,30) 
    WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 12 THEN CONCAT(new_TarikhSebenarTamatPengajian,31)
    END AS newdate 
FROM TEMP_AKAUN_MARA

edited Nov 11 '14 at 04:27

answered Nov 11 '14 at 02:48

Edrich

232
2
8

I have try but not working Incorrect syntax near the keyword 'CASE'. – MUHD MAN Nov 11 '14 at 03:59
I have tried that code in my mysql and it works fine, may I know your exact columns in table? – Edrich Nov 11 '14 at 04:00
Table : dbo.TEMP_AKAUN_MARA Field : new_TarikhSebenarTamatPengajian – MUHD MAN Nov 11 '14 at 04:01
Is it really your field names? I thought your field for date is crse_date – Edrich Nov 11 '14 at 04:03
Sory Mr Edrich. I already rename CRSE_DATE to new_TarikhSebenarTamatPengajian. So i write --> WHEN RIGHT(new_TarikhSebenarTamatPengajian,2) = 01 THEN CONCAT(new_TarikhSebenarTamatPengajian,31) – MUHD MAN Nov 11 '14 at 04:04
Ah I see, There is no problem regarding that as long you change also with the query but that fieldnames are not advisable. can you comment the full query after the changes? – Edrich Nov 11 '14 at 04:07
@MUHDMAN you missed the comma(,) before CASE, please see the update on my answer above. – Edrich Nov 11 '14 at 04:19
Error here: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the concat operation. – MUHD MAN Nov 11 '14 at 04:28

SQL field add date change format to YYYYMMDD

3 Answers3