none
Change varchar column with numbers(290119) to date column(ddmmyyyy)

    Question

  • Hi,

    Is it possible to change the varchar column to a date column. I currently have the date as a varchar, 290319(ddmmyy), which I want to change to date(ddmmyyyy)

    Would it be possible to do it in the first select?

    Thanks in advance

    Thursday, May 16, 2019 6:03 AM

Answers

  • Hi Sarat,

    Thanks for your answer - it works. Would it then be possible to cast/convert varchar to a date in the same select?

    This might work,

     select c1,newC1, 
     PARSENAME(newC1,1)+'.'+PARSENAME(newC1,2)+'.'+PARSENAME(newC1,3)
     ,cast(PARSENAME(newC1,1)+'.'+PARSENAME(newC1,2)+'.'+PARSENAME(newC1,3) as date)
     from (
    select c1,   STUFF(STUFF(STUFF(c1,5,len(c1),'20'+ RIGHT( c1 ,2)),3,0,'.'),6,0,'.')
     as newC1
    from test
       )A 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 16, 2019 7:00 AM
    Answerer
  • If the year value in the varchar column is to be considered as in the 21st century, even if it is greater than the current year, here is a suggestion:

    -- code #1
    SELECT try_convert (date, 
                        substring (varchar_column, 1, 2) + '/' 
                         + substring (varchar_column, 3, 2) + '/20'
                         + substring (varchar_column, 5, 2),
                        103)
      from table;

    But if one should consider the previous century when the year value of the varchar column is greater than the current year, here is another suggestion:

    -- code #2
    SELECT try_convert (date, 
                        substring (varchar_column, 1, 2) + '/' 
                         + substring (varchar_column, 3, 2) 
                         + case when cast (substring (varchar_column, 5, 2) as int) > year(current_timestamp) % 100
                             then '/19' else '/20' end 
                         + substring (varchar_column, 5, 2),
                        103)
      from table;


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Tuesday, May 21, 2019 12:49 PM
    Answerer

All replies

  • Its hard to get exact yyyy from yy as in this case it could be 1919 or 2019. 

    Try this if it works ,

    create table test( c1 varchar(10))
    insert into test values ('290319')
    
    select c1, STUFF(c1,5,len(c1),'20'+ RIGHT( c1 ,2))  from test 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 16, 2019 6:29 AM
    Answerer
  • Hi Sarat,

    Thanks for your answer - it works. Would it then be possible to cast/convert varchar to a date in the same select?

    Thursday, May 16, 2019 6:39 AM
  • yes its possible , but with the format you are saving date value it can't be.

    You have to tweak the format and use cast or convert. You can use TRY_CAST() to check if the data you have currently can be converted 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 16, 2019 6:47 AM
    Answerer
  • Hi Sarat,

    Thanks for your answer - it works. Would it then be possible to cast/convert varchar to a date in the same select?

    This might work,

     select c1,newC1, 
     PARSENAME(newC1,1)+'.'+PARSENAME(newC1,2)+'.'+PARSENAME(newC1,3)
     ,cast(PARSENAME(newC1,1)+'.'+PARSENAME(newC1,2)+'.'+PARSENAME(newC1,3) as date)
     from (
    select c1,   STUFF(STUFF(STUFF(c1,5,len(c1),'20'+ RIGHT( c1 ,2)),3,0,'.'),6,0,'.')
     as newC1
    from test
       )A 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 16, 2019 7:00 AM
    Answerer
  • If the year value in the varchar column is to be considered as in the 21st century, even if it is greater than the current year, here is a suggestion:

    -- code #1
    SELECT try_convert (date, 
                        substring (varchar_column, 1, 2) + '/' 
                         + substring (varchar_column, 3, 2) + '/20'
                         + substring (varchar_column, 5, 2),
                        103)
      from table;

    But if one should consider the previous century when the year value of the varchar column is greater than the current year, here is another suggestion:

    -- code #2
    SELECT try_convert (date, 
                        substring (varchar_column, 1, 2) + '/' 
                         + substring (varchar_column, 3, 2) 
                         + case when cast (substring (varchar_column, 5, 2) as int) > year(current_timestamp) % 100
                             then '/19' else '/20' end 
                         + substring (varchar_column, 5, 2),
                        103)
      from table;


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Tuesday, May 21, 2019 12:49 PM
    Answerer