none
changing date format from yyyy/mm/dd to mm/dd/yyyy

    Question

  • I use the following statment:
    MAX(case when A.[NGT_KYC_QUES_PROPERTY]='ResolvedDate' then CAST(SUBSTRING([NGT_KYC_QUES_ANSWER],5,2) + '-' + SUBSTRING([NGT_KYC_QUES_ANSWER],7,2) + '-' + SUBSTRING([NGT_KYC_QUES_ANSWER],1,4) AS DATE) ELSE '' END)AS ResolvedDate,

    to change date in text field (20140303T144253.253 GMT) to the following format:

    2014/03/03

    I would like to put it in the following format 03/03/2014 mm/dd/yyyy.

    is it possible to do this all in one statement?

    Thursday, April 10, 2014 6:23 PM

Answers

All replies

  • declare @s varchar(26)='20140313T144253.253 GMT'
     
     
     select Convert(char(10), Cast(substring(@s,1,4)+'-'+substring(@s,5,2)+'-'+substring(@s,7,5)+ ':'+substring(@s,12,2)+':'+substring(@s,14,6) as datetime),101)

    Thursday, April 10, 2014 6:47 PM
    Moderator
  • Actually you code does not convert that string to a string of "yyyy/mm/dd", it converts the value to a DATE. Your display must be changing it to that format.  Do not confuse the display format with the storage format.

    PS.  You do not need to do the substrings.

    CONVERT(varchar(10), CONVERT(date, LEFT([NGT_KYC_QUES_ANSWER],8), 112), 101)

    Thursday, April 10, 2014 6:55 PM
    Moderator
  • thank you so much for the response, I am not sure how to combine it with the statment I am using?

    MAX(case when A.[NGT_KYC_QUES_PROPERTY]='ResolvedDate' then CAST(SUBSTRING([NGT_KYC_QUES_ANSWER],5,2) + '-' + SUBSTRING([NGT_KYC_QUES_ANSWER],7,2) + '-' + SUBSTRING([NGT_KYC_QUES_ANSWER],1,4) AS DATE) ELSE '' END)AS ResolvedDate,

    Thursday, April 10, 2014 7:44 PM
  • Convert(char(10), MAX(case when A.[NGT_KYC_QUES_PROPERTY]='ResolvedDate' 
    then cast(left(NGT_KYC_QUES_ANSWER,8) as datetime) ELSE null END), 101)
    AS ResolvedDate

    Thursday, April 10, 2014 8:07 PM
    Moderator
  • Thank you , works fine
    Thursday, April 10, 2014 8:12 PM