none
Text column date format not like before RRS feed

  • Question

  • Hi guys

    I have SQL Server 2012, we have column am_label2 and its data type is varchar, we have updated to  SQL Server 2012 and after that we are facing some problem with this column value, see below for sample:

    Previously we have data in below format:

    Previous data sample

    12/1/2019 1:00:00 AM

    12/1/2019

    Current data sample

    2019-12-21

    2019-12-21 1:00:00 AM

    I use the following command to filter date previously, see below:

    where 
    (month((CAST(parsename(replace(g.am_label2, '/', '.'), 3)+'/'+parsename(replace(am_label2, '/', '.'), 2)+'/'+parsename(replace(am_label2, '/', '.'), 1) AS 
    datetime)))               = month(dateadd(month, -1, getdate())) 
    
    AND year((CAST(parsename( 
    replace(am_label2, '/', '.'), 3)+'/'+parsename(replace(am_label2, '/', '.'), 2)+'/'+parsename(replace(am_label2, '/', '.'), 1) AS datetime)))   = year( 
    dateadd(month, -1, getdate()))) 
    

    Now because of data format change in am_label2 column, the below condition not true, now my question is that how to fix this problem?. Thanks

    Muz

    Thursday, January 9, 2020 12:28 AM

Answers

  • Hi,

    create table #temp1 (col1 varchar(200))
    insert #temp1 values ('12/1/2019 1:00:00 AM'),('12/1/2019')

    select *, convert(varchar(200),format(cast(col1 as datetime),'yyyy-MM-dd hh:mm:ss tt'))
    from #temp1

    Mark as answer if it works. Thanks.

    • Marked as answer by Muzokhh Monday, February 3, 2020 6:16 PM
    Thursday, January 9, 2020 3:44 AM

All replies

  • Use a datetime data type to hold your datetime value. It will solve your problem. Don't use a string type to store your datetime in your table.
    • Proposed as answer by Lily Lii Friday, January 10, 2020 1:33 AM
    Thursday, January 9, 2020 1:35 AM
    Moderator
  • Hi Muz,

    May I ask if you have back up your original data before updating SQL Server 2012? It seems like the data completely changed (12/1/2019 -> 2019-12-21).

    If yes, is it possible for you to roll back the previous version and convert varchar to datetime? 

    update mytable set am_label2=cast(am_label2 as datetime2)

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Friday, January 10, 2020 1:33 AM
    Thursday, January 9, 2020 3:11 AM
  • Hi,

    create table #temp1 (col1 varchar(200))
    insert #temp1 values ('12/1/2019 1:00:00 AM'),('12/1/2019')

    select *, convert(varchar(200),format(cast(col1 as datetime),'yyyy-MM-dd hh:mm:ss tt'))
    from #temp1

    Mark as answer if it works. Thanks.

    • Marked as answer by Muzokhh Monday, February 3, 2020 6:16 PM
    Thursday, January 9, 2020 3:44 AM
  • Hi Muz,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 10, 2020 1:35 AM