none
Varchar to datetime conversion

    Question

  • I have a column which has 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using 

    update tablename
    set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' + 
    SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));

    and then 

    alter table tablename

    alter columnname datetime;

    but later it shows up the error

    Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    How do I change it any other opinion or any modification for the above query. Please help. Thank you.

    Saturday, July 19, 2014 9:58 PM

Answers

  • Hello James,

    Thank you for your query I could see the date in 2006-05-05 04:34:00.000, but my problem is I have more than 10,000 rows in it. As per my understanding this query would mean I take each row and individually convert them using convert() right ?  

    Try below , As James recommended please Move data to other table(temp) then alter the table and push back the data.

    declare @dt table (crdate varchar(50))
    insert into @dt
    select '05MAY2006:04:34:00.000000' union all
    select '05JANUARY2006:04:34:00.000000' union all
    select '05NOVEMBER2006:04:34:00.000000' union all
    select '05NOVEMBER2006:25:34:00.000000' 
    
    select RIGHT(crdate,7),RIGHT(crdate,16), replace(crdate,right(crdate,16),' ') , RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8) from @dt
    
    -- Step 1 verify the date are looks good
    select crdate,convert(varchar,convert(datetime,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)),121)
    from @dt where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=1
    
    --step 2 update the date
    update @dt set  crdate = convert(varchar,convert(datetime,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)),121)
    where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=1
    
    --Data that throw error after update
    --Step 3 check whether yuo have any date found in below query, then you may need to convert that data also to date before converting the column to datatime
    select crdate,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)
    from @dt where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=0

    Thanks

    Saravana Kumar C

    Sunday, July 20, 2014 6:15 AM
  • I think you need to convert it to datetime2 if you want preserve the full precision of the values as is

    and you dont have to convert each value individually.

    You can just do as per below illustration

    --Just creating a temp table to correspond to your table for illustration. you dont need this. you can use your table itself instead
    if OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
    
    create table #t
    (
    dt varchar(25)
    )
    
    --inserting your example value + two others for illustration
    insert #t
    values ('05MAY2006:04:34:00.000000'),
    ('13JUN2010:22:35:46.000000'),
    ('13MAR2012:03:12:34.000000')
    
    
    --update statement to change format to what datetime2 fields expect
    UPDATE #t
    SET dt = CONVERT(datetime2,STUFF(STUFF(STUFF(dt,3,0,' '),7,0,' '),12,1,' '),113)
    
    --see if change is applied
    SELECT * FROM #t
    
    --now change the column type as datetime2
    ALTER TABLE #t ALTER COLUMN dt datetime2

    However if you still want to stick to datetime type instead of datetime2 you need to first reduce precision of value passed to match datetime precision

    and it will look like below

    if OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
    create table #t
    (
    dt varchar(25)
    )
    
    insert #t
    values ('05MAY2006:04:34:00.000000'),
    ('13JUN2010:22:35:46.000000'),
    ('13MAR2012:03:12:34.000000')
    
    UPDATE #t
    SET dt = CONVERT(datetime,STUFF(STUFF(STUFF(LEFT(dt,22),3,0,' '),7,0,' '),12,1,' '),113)
    
    
    ALTER TABLE #t ALTER COLUMN dt datetime
    
    SELECT * FROM #t

    Hope this explains

    Also for future needs please make sure you use date related fields itself to store date values wherever possible and even in cases where you want to pass date values as varchar make sure you pass them in unambiguous universally accepted consistent format as per below

    http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 20, 2014 6:30 AM
  • Hi Vinny,

    You will not convert it individually.

    It will run ok as long as the format 05MAY2006:04:34:00.000000(05MONTH2014:04:34:00.000000)

    If the table has columnname in datetime data type already.

    Try this code:

    update tablename
    set columnname =  convert(datetime,replace(columnname ,right(columnname ,16),' ') + RIGHT(REPLACE(columnname ,RIGHT(columnname ,7),''),7))

    If the table columnname is still in varchar data type

    1. Move the data in a temporary table.

    2. Remove the data in the tablename

    3. alter the columnname change data type to datetime

    4. Move back the date from temporary table to tablename

    --Assuming your table is the VARCHARTABLE this will work even it has a 10,000 records

    create table VARCHARTABLE
    (
    columnname varchar(50)
    )
    insert into VARCHARTABLE values('05MAY2006:04:34:00.000000')
    insert into VARCHARTABLE values('05JANUARY2006:04:34:00.000000')
    insert into VARCHARTABLE values('05NOVEMBER2006:04:34:00.000000')
    --1
    select * into #temp from VARCHARTABLE
    --2
    truncate table VARCHARTABLE
    --3
    alter table VARCHARTABLE
    alter column columnname datetime
    select * from VARCHARTABLE
    --4
    insert into VARCHARTABLE select convert(datetime,replace(columnname,right(columnname,16),' ') + RIGHT(REPLACE(columnname,RIGHT(columnname,7),''),7)) from #temp
    select * from VARCHARTABLE

    Now that columnname is already a datetime you will insert the new data

    insert into VARCHARTABLE values(convert(datetime,replace('05NOVEMBER2006:04:34:00.000000',right('05NOVEMBER2006:04:34:00.000000',16),' ') + RIGHT(REPLACE('05NOVEMBER2006:04:34:00.000000',RIGHT('05NOVEMBER2006:04:34:00.000000',7),''),7)))



    Sunday, July 20, 2014 6:33 AM
  • UPDATE myTable SET targetColumn = STUFF ( targetColumn , 10, 1, ' ') -- ddmmmyyyy:hh:mm:ss.nnnnnn -- \ -- this colon is extra which is at 10th position

    ALTER TABLE tablename

    ALTER COLUMN columnnamedatetime2;


    vinny

    • Marked as answer by VinnyVedi Tuesday, August 12, 2014 9:15 PM
    Tuesday, August 12, 2014 9:15 PM

All replies

  • Alter the table first.

    Move data to other table(temp) then alter the table and push back the data.

    this might code help you.

    declare @dt varchar(50)
    set @dt= '05MAY2006:04:34:00.000000'
    --select (SUBSTRING(@dt,1,2) + '-' + SUBSTRING(@dt,3,3) + '-' + 
    --SUBSTRING(@dt,6,4) + ' ' + SUBSTRING(@dt,11,8))
    select convert(datetime,replace(@dt,right(@dt,16),' ') + RIGHT(REPLACE(@dt,RIGHT(@dt,7),''),7))
    
    set @dt= '05JANUARY2006:04:34:00.000000'
    select convert(datetime,replace(@dt,right(@dt,16),' ') + RIGHT(REPLACE(@dt,RIGHT(@dt,7),''),7))
    set @dt= '05NOVEMBER2006:04:34:00.000000'
    select convert(datetime,replace(@dt,right(@dt,16),' ') + RIGHT(REPLACE(@dt,RIGHT(@dt,7),''),7))

    Then insert the data

    INSERT INTO tablename(col1,columnname)
    select col1,convert(datetime,replace(columnname,right(columnname,16),' ') + RIGHT(REPLACE(columnname,RIGHT(columnname,7),''),7)) from #temp

    Try it.


    Sunday, July 20, 2014 3:56 AM
  • Hello James,

    Thank you for your query I could see the date in 2006-05-05 04:34:00.000, but my problem is I have more than 10,000 rows in it. As per my understanding this query would mean I take each row and individually convert them using convert() right ?  

    Sunday, July 20, 2014 4:31 AM
  • Hello James,

    Thank you for your query I could see the date in 2006-05-05 04:34:00.000, but my problem is I have more than 10,000 rows in it. As per my understanding this query would mean I take each row and individually convert them using convert() right ?  

    Try below , As James recommended please Move data to other table(temp) then alter the table and push back the data.

    declare @dt table (crdate varchar(50))
    insert into @dt
    select '05MAY2006:04:34:00.000000' union all
    select '05JANUARY2006:04:34:00.000000' union all
    select '05NOVEMBER2006:04:34:00.000000' union all
    select '05NOVEMBER2006:25:34:00.000000' 
    
    select RIGHT(crdate,7),RIGHT(crdate,16), replace(crdate,right(crdate,16),' ') , RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8) from @dt
    
    -- Step 1 verify the date are looks good
    select crdate,convert(varchar,convert(datetime,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)),121)
    from @dt where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=1
    
    --step 2 update the date
    update @dt set  crdate = convert(varchar,convert(datetime,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)),121)
    where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=1
    
    --Data that throw error after update
    --Step 3 check whether yuo have any date found in below query, then you may need to convert that data also to date before converting the column to datatime
    select crdate,replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8)
    from @dt where ISDATE(crdate)= 0
    and ISDATE(replace(crdate,right(crdate,16),' ') + RIGHT(REPLACE(crdate,RIGHT(crdate,7),''),8))=0

    Thanks

    Saravana Kumar C

    Sunday, July 20, 2014 6:15 AM
  • I think you need to convert it to datetime2 if you want preserve the full precision of the values as is

    and you dont have to convert each value individually.

    You can just do as per below illustration

    --Just creating a temp table to correspond to your table for illustration. you dont need this. you can use your table itself instead
    if OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
    
    create table #t
    (
    dt varchar(25)
    )
    
    --inserting your example value + two others for illustration
    insert #t
    values ('05MAY2006:04:34:00.000000'),
    ('13JUN2010:22:35:46.000000'),
    ('13MAR2012:03:12:34.000000')
    
    
    --update statement to change format to what datetime2 fields expect
    UPDATE #t
    SET dt = CONVERT(datetime2,STUFF(STUFF(STUFF(dt,3,0,' '),7,0,' '),12,1,' '),113)
    
    --see if change is applied
    SELECT * FROM #t
    
    --now change the column type as datetime2
    ALTER TABLE #t ALTER COLUMN dt datetime2

    However if you still want to stick to datetime type instead of datetime2 you need to first reduce precision of value passed to match datetime precision

    and it will look like below

    if OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t
    create table #t
    (
    dt varchar(25)
    )
    
    insert #t
    values ('05MAY2006:04:34:00.000000'),
    ('13JUN2010:22:35:46.000000'),
    ('13MAR2012:03:12:34.000000')
    
    UPDATE #t
    SET dt = CONVERT(datetime,STUFF(STUFF(STUFF(LEFT(dt,22),3,0,' '),7,0,' '),12,1,' '),113)
    
    
    ALTER TABLE #t ALTER COLUMN dt datetime
    
    SELECT * FROM #t

    Hope this explains

    Also for future needs please make sure you use date related fields itself to store date values wherever possible and even in cases where you want to pass date values as varchar make sure you pass them in unambiguous universally accepted consistent format as per below

    http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 20, 2014 6:30 AM
  • Hi Vinny,

    You will not convert it individually.

    It will run ok as long as the format 05MAY2006:04:34:00.000000(05MONTH2014:04:34:00.000000)

    If the table has columnname in datetime data type already.

    Try this code:

    update tablename
    set columnname =  convert(datetime,replace(columnname ,right(columnname ,16),' ') + RIGHT(REPLACE(columnname ,RIGHT(columnname ,7),''),7))

    If the table columnname is still in varchar data type

    1. Move the data in a temporary table.

    2. Remove the data in the tablename

    3. alter the columnname change data type to datetime

    4. Move back the date from temporary table to tablename

    --Assuming your table is the VARCHARTABLE this will work even it has a 10,000 records

    create table VARCHARTABLE
    (
    columnname varchar(50)
    )
    insert into VARCHARTABLE values('05MAY2006:04:34:00.000000')
    insert into VARCHARTABLE values('05JANUARY2006:04:34:00.000000')
    insert into VARCHARTABLE values('05NOVEMBER2006:04:34:00.000000')
    --1
    select * into #temp from VARCHARTABLE
    --2
    truncate table VARCHARTABLE
    --3
    alter table VARCHARTABLE
    alter column columnname datetime
    select * from VARCHARTABLE
    --4
    insert into VARCHARTABLE select convert(datetime,replace(columnname,right(columnname,16),' ') + RIGHT(REPLACE(columnname,RIGHT(columnname,7),''),7)) from #temp
    select * from VARCHARTABLE

    Now that columnname is already a datetime you will insert the new data

    insert into VARCHARTABLE values(convert(datetime,replace('05NOVEMBER2006:04:34:00.000000',right('05NOVEMBER2006:04:34:00.000000',16),' ') + RIGHT(REPLACE('05NOVEMBER2006:04:34:00.000000',RIGHT('05NOVEMBER2006:04:34:00.000000',7),''),7)))



    Sunday, July 20, 2014 6:33 AM
  • Hey Saravana, 

    Thank you for your reply, but I ended up with many syntax error while I was working with my columns. But the example you have shown here works well. 

    Monday, July 21, 2014 12:56 AM
  • Hello Visakh,

    I did and example you showed works. Also I tried another query where I converted my datatype to datetime2. Because others would just give me sytanx errors while I was trying to use it on my columns. 

    Monday, July 21, 2014 12:58 AM
  • Thank you James but as I said I couldn't work that out with my columns. Your solution made a lot of sense though, but as I am no expert in SQL I couldn't debug the errors.
    Monday, July 21, 2014 1:00 AM
  • Hey Saravana, 

    Thank you for your reply, but I ended up with many syntax error while I was working with my columns. But the example you have shown here works well. 

    HI

    Please Post the actual Table name and Column name , i can modify and post it for you to check.

    Thanks

    Saravana Kumar C

    Monday, July 21, 2014 3:23 AM
  • Hello Visakh,

    I did and example you showed works. Also I tried another query where I converted my datatype to datetime2. Because others would just give me sytanx errors while I was trying to use it on my columns. 

    So does that mean your issue is solved? Do you still need assistance with anything?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 21, 2014 3:29 AM
  • UPDATE myTable SET targetColumn = STUFF ( targetColumn , 10, 1, ' ') -- ddmmmyyyy:hh:mm:ss.nnnnnn -- \ -- this colon is extra which is at 10th position

    ALTER TABLE tablename

    ALTER COLUMN columnnamedatetime2;


    vinny

    • Marked as answer by VinnyVedi Tuesday, August 12, 2014 9:15 PM
    Tuesday, August 12, 2014 9:15 PM