datefield - SQL field add date change format to YYYYMMDD -

datefield - SQL field add date change format to YYYYMMDD -

i have 1 field date contains year , months. field name new_tarikhsebenartamatpengajian. exmaple of info 199110 (format:yyyymm). want add together date set new format require (yyyymmdd). available info can add together date follow end of month month. illustration 199101 = 19910130 or 199102 = 19910228 or 199103 = 19910231

this code not work me

select dmstag.dbo.temp_akaun_mara.new_tarikhsebenartamatpengajian case when right(new_tarikhsebenartamatpengajian,2) = 01 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 02 concat(new_tarikhsebenartamatpengajian,28) when right(new_tarikhsebenartamatpengajian,2) = 03 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 04 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 05 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 06 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 07 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 08 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 09 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 10 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 11 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 12 concat(new_tarikhsebenartamatpengajian,31) end newdate [dbo].[temp_akaun_mara]

try one:

select id,crse_date, case when right(crse_date,2) = 01 concat(crse_date,31) when right(crse_date,2) = 02 concat(crse_date,28) when right(crse_date,2) = 03 concat(crse_date,31) when right(crse_date,2) = 04 concat(crse_date,30) end newdate yourtable after alter table. here new query problem takenote : field name not advisable select new_tarikhsebenartamatpengajian, case when right(new_tarikhsebenartamatpengajian,2) = 01 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 02 concat(new_tarikhsebenartamatpengajian,28) when right(new_tarikhsebenartamatpengajian,2) = 03 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 04 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 05 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 06 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 07 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 08 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 09 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 10 concat(new_tarikhsebenartamatpengajian,31) when right(new_tarikhsebenartamatpengajian,2) = 11 concat(new_tarikhsebenartamatpengajian,30) when right(new_tarikhsebenartamatpengajian,2) = 12 concat(new_tarikhsebenartamatpengajian,31) end newdate temp_akaun_mara

sql datefield