none
Getting error while converting datetime to chracter string

    Question

  • Hi

    while executing below query having error

    Select convert(datetime,SUBSTRING('12/29/13',1,6))+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 

    doing a bulk insert and selecting the date time getting conversion failed when converting date and/or time from character string.  the result should be 2013-12-29 04:34:00 

    please help

    Thanks

    Mary Abraham

                

     


    Mary Sunish

    Saturday, January 18, 2014 3:57 AM

Answers

  • Hi Mary

    When you are using a column name and that column type is datetime, then you don't need to do the inner convert which HuaMin Chen wrote in his query. Using Bulk Insert will get the type according your format file (I hope you do use a format file, and if not then i recommend to).

    In this case you can use HuaMin's query without the inner CONVERT like this:

    Select convert(varchar,rawdata,1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 
    You can check it with a variable in this query:
    declare @rawdata datetime = CONVERT(datetime,'12/29/13',1)
    Select convert(varchar,@rawdata,1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 

    But this will not bring you back the format that you want

    check is this is what you are looking for:

    declare @rawdata datetime = CONVERT(datetime,'12/29/13',1)
    select CONVERT(VARCHAR(23), @rawdata, 121) as DOS

    * If you are using SQL 2012 then you can use FORMAT function

    * if you don't need the millisecond then you can use 120 instead of 121
    120 format is: YYYY-MM-DD HH:MI:SS(24h)
    121 format is: YYYY-MM-DD HH:MI:SS.MMM(24h)


    [Personal Site] [Blog] [Facebook]signature

    Saturday, January 18, 2014 9:43 AM
    Moderator
  • Hello,

    Please refer to the following statement:

    select cast (('12/29/13'+ ' '+substring('0434',1,2)+':'+ substring('0434',3,4)) as smalldatetime) as DOS

    Result: 2013-12-29 04:34:00

    Reference:CAST and CONVERT

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by MaryAbraham Thursday, January 23, 2014 12:25 AM
    Monday, January 20, 2014 1:00 PM
    Moderator
  • The topic CAST and CONVERT in Books Online, can inform you which code you can use to convert that format to datetime. I believe there has already been samples of this given in the thread. No need to substring acrobatics.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by MaryAbraham Thursday, January 23, 2014 12:25 AM
    Sunday, January 19, 2014 8:41 PM

All replies

  • Try this instead

    Select convert(varchar,CONVERT(datetime,'12/29/13',1),1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 


    Many Thanks & Best Regards, Hua Min



    Saturday, January 18, 2014 4:05 AM
  • You should be using DATE data type and not strings. And why is that string no in ISO-8601 display format? This mess is 1960's COBOL, where dates were strings in a local dialect (looks like US conventions). The best way is to repair the raw data before you do the bulk load. SQL is not a  good string language. 

    We do not use the old 1970's Sybase CONVERT string function today. We have CAST (something_date AS DATE) or CAST (something_date AS DATETIME2(0)) now. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, January 18, 2014 4:20 AM
  • Hi

    I am using this in a store procedure where I bulk insert and the select statement is

    Select convert(datetime,Substring(rawdata,40,6)+'20' +SUBSTRING('Rawdata',47,4) + ':00' as DOS 

    with out substring how it is going to work?

    I am using CONVERT(datetime,SUBSTRING(rawdata,40,6)+'20'+SUBSTRING(rawdata,46,2))asDOS,  in my store procedure and in the bulk insert creating temp table as varchar (200)

    please advice


    Mary Sunish

    Saturday, January 18, 2014 4:33 AM
  • Hi Mary

    When you are using a column name and that column type is datetime, then you don't need to do the inner convert which HuaMin Chen wrote in his query. Using Bulk Insert will get the type according your format file (I hope you do use a format file, and if not then i recommend to).

    In this case you can use HuaMin's query without the inner CONVERT like this:

    Select convert(varchar,rawdata,1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 
    You can check it with a variable in this query:
    declare @rawdata datetime = CONVERT(datetime,'12/29/13',1)
    Select convert(varchar,@rawdata,1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS 

    But this will not bring you back the format that you want

    check is this is what you are looking for:

    declare @rawdata datetime = CONVERT(datetime,'12/29/13',1)
    select CONVERT(VARCHAR(23), @rawdata, 121) as DOS

    * If you are using SQL 2012 then you can use FORMAT function

    * if you don't need the millisecond then you can use 120 instead of 121
    120 format is: YYYY-MM-DD HH:MI:SS(24h)
    121 format is: YYYY-MM-DD HH:MI:SS.MMM(24h)


    [Personal Site] [Blog] [Facebook]signature

    Saturday, January 18, 2014 9:43 AM
    Moderator
  • Since you cannot use substring or any other function with the BULK INSERT command, I assume that you are in fact doing a regular INSERT from a SELECT statement.

    Since we don't see exactly what's in your rawdata column it's difficult to tell exactly what you should write. But SUBSTRING('12/29/13',1,6) is 12/29/ and if you add 20 to that, you are certainly not going to end up with a date from 2013. The result of SUBSTRING('0434',1,4) is of course 0434, so the complete string you get is 12/29/200434 which not much of a date at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 18, 2014 11:06 AM
  • Hi

    Rawdata has dateofservice column that I what I am trying to convert

    The storeprocedure has creating temp table data type varchar (200) and then bulk insert then I am inserting to the database table and then using Select convert(datatime,rawdata,1)+ '20' + SUBSTRING('0434',1,4) + ':00' as DOS

    Hope now the question is clear

    Thanks,

    Mary Abraham 


    Mary Sunish

    Sunday, January 19, 2014 12:40 PM
  • Well, we still do not know the format of that column, so we cannot help you any further than we have done so far.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 19, 2014 5:42 PM
  • Hi.

    the format of the coloumn is varchar and in the table it is datetime

    the date formate is 12/29/13


    Mary Sunish

    Sunday, January 19, 2014 8:08 PM
  • The topic CAST and CONVERT in Books Online, can inform you which code you can use to convert that format to datetime. I believe there has already been samples of this given in the thread. No need to substring acrobatics.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by MaryAbraham Thursday, January 23, 2014 12:25 AM
    Sunday, January 19, 2014 8:41 PM
  • Hello,

    Please refer to the following statement:

    select cast (('12/29/13'+ ' '+substring('0434',1,2)+':'+ substring('0434',3,4)) as smalldatetime) as DOS

    Result: 2013-12-29 04:34:00

    Reference:CAST and CONVERT

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by MaryAbraham Thursday, January 23, 2014 12:25 AM
    Monday, January 20, 2014 1:00 PM
    Moderator
  • Thanks for all who helped me.

    Mary Sunish

    Thursday, January 23, 2014 12:26 AM