CODEWITHSUNDEEP
sqldatefield
MUHD MAN
MUHD MAN

Reputation: 55

SQL field add date change format to YYYYMMDD

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]

Upvotes: 0

Views: 208

Answers (3)

NeedAnswers
NeedAnswers

Reputation: 1435

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

Upvotes: 1

Edrich
Edrich

Reputation: 242

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

Upvotes: 0

Hardik Parmar
Hardik Parmar

Reputation: 1051

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

Upvotes: 0

Related Questions