Convert Text to Date DataType

  • Hello Everyone,

    I have an sql table which stores dates from some other system as text.

    Eg.: DD/MM/YYYY ,31/12/2012.

    I have made a query which fetches these dates and put them in a temp table and i have declared varchar(10) as Datatype for this coloumn.

    But i want this coloumn to be in date data type.

    I need this because i am using this query in SSRS and when i export the result to excel it needs to be in Date DT.

    Thanks

    Regards

    Ravi T

  • Can't you change the data type of the column in your temp table to accomodate?

    Mark

  • Try something like this:

    select CONVERT(date, mydate, 103)

    from mytable

  • Hi Mark,

    yes i can do that but,how will sql know that my text is in dd/mm/yyyy format and not in mm/dd/yyyy or smthing else.

    rookie here

    regards

    Ravi T

  • santa326 (8/14/2012)


    Hi Mark,

    yes i can do that but,how will sql know that my text is in dd/mm/yyyy for and not in mm/dd/yyyy or smthing else.

    regards

    Ravi T

    The third value in the convert statement tells SQL Server the string value of the data/time is in dd/mm/yyyy format.

    For more information, read this:

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

  • HI all,

    Ive hit another wall!!!!!!!!.......i have another data set which has date stored as text and the format is dd/mm/yy.Now how do i go about converting this into date datatype?

    I have gone through few date time system..n i dint find any number for this type of date.

    Regards

    Ravi T

  • Instead of 103 use 03

    DECLARE @D CHAR(10) = '17/08/12'

    SELECT CONVERT(date, @D, 03)

    --Vadim R.

  • rVadim (8/16/2012)


    Instead of 103 use 03

    DECLARE @D CHAR(10) = '17/08/12'

    SELECT CONVERT(date, @D, 03)

    03? That is old ambulance number in Russia, you can use just 3 ...:hehe:

    Actually, SQL can convert '17/08/12' and '17/08/2012' into date without help of the third (dateformat) parameter. You are facing the issue due to the default format on your server is in American style (mm/dd/yyy). You can easily change it any time to whatever you like. The following example will explain it to you in a more visual way:

    SET DATEFORMAT DMY

    DECLARE @D CHAR(10) = '17/08/12'

    SELECT CONVERT(date, @D)

    SET @D = '17/08/2012'

    SELECT CONVERT(date, @D)

    GO

    SET DATEFORMAT MDY

    DECLARE @D CHAR(10) = '8/17/12'

    SELECT CONVERT(date, @D)

    SET @D = '08/17/2012'

    SELECT CONVERT(date, @D)

    GO

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (8/17/2012)[hr03? That is old ambulance number in Russia, you can use just 3 ...:hehe:

    Oh yea, 03 ?????? ??????. Long time ago, in another life...

    --Vadim R.

  • CELKO (8/18/2012)


    CAST (SUBSTRING (IN_STR, 7, 4)+'-'

    +SUBSTRING (IN_STR, 3, 2))+'-'

    +SUBSTRING (IN_STR, 1, 2)

    AS DATE)

    cut up the string and put the Fields into ISO-8601 formatm then cast to DATE.

    Really, why do all the string spliting and concatenation when the a) the text string is already in dd/mm/yy format, and b) you can use the format codes (3 or 103) in the convert statement anyway? A lot of extra work for no real value.

  • 1. CONVERT() is proprietary and will not port; substring and concatenation are Standard.

    Port to where? SYBASE? DBASE? MongoDB? Mars? Alpha Centauri?

    Do you seriously think that it would help much when porting any serious SQL Server database into something absolutely different? Do you often hear about projects porting SQL into something else? (The key word is "often"). In my, around 20 years of experience it was once and it was into Oracle, where using "proprietary" T-SQL functions was the very last problem, compare with others...

    CONVERT is so MS T-SQL, no one in their right mind will believe that it will be dropped "in the next version".

    Will you advise against using new non-standard FORMAT function in SQL2012?

    2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.

    Fast? Can you prove, please? So far all test ever been done on this did show no difference.

    At the end, to convert string datatype into date datatype, engine uses CASTing in both cases! CONVERT does manipulation on string for you based on the format code, and it is in no way slower than multiple string concatenation and "substringing" in T-Sql (you know well, that behind of scene, sql uses immutable strings for this sort of operations - so, they not very fast...).

    Simple? One function instead of "substringed" noodles - yeah, that is mater of taste...

    3. Neither of those codes returns the ISO-8601 display format used in ANSI/ISO Standard SQL -- you want 126.

    If I'm right the OP wanted to convert string to date, he didn't asked for any particular display format. You cannot guess what this data will be used for. I would think he is loading the table, so he doesn't care about displaying it at this point.

    4. We want to CAST ( xxx AS DATE) so we can do temporal math with it, so get to the goal in one step, not two.

    Here I'm lost a bit. You are saying that the way you shown it happens in one step, but using CONVERT means doing it in two steps? May I ask how do you count "steps"? :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • CELKO (8/19/2012)


    why do all the string spliting and concatenation when the a) the text string is already in dd/mm/yy format, and b) you can use the format codes (3 or 103) in the convert statement anyway?

    1. CONVERT() is proprietary and will not port; substring and concatenation are Standard.

    The idea of always writing 100% portable code is an academic fallacy. I am sorry but I am going to write code using the all the tools available to me to provide my employer with the best possible solution. I have yet to work for an employer that is concerned with the possibility of having to port their database from one RDBMS environment to another on any regular basis.

    2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.

    Okay, now you are making a claim that one method is faster than another yet fail to back it up. I was too tired last night to write up a formal test suite to check your hypthosis but did do a short informal test. Sorry to burst to bubble here, but using the CONVERT function, written CONVERT(DATE,MyDateStr,103), ran faster than the SUBSTRING/CONCATENATE/CAST, written CAST(SUBSTRING(MyDateStr,7,4) + '-' + SUBSTRING(MyDateStr,4,2) + '-' + SUBSTRING(MyDateStr,1,2) as DATE), using a 1,000,000 row table was actually faster on all but 1 run. I reran the queries 5 times, so that is 80% of the time the CONVERT beat your solution. Hopefully I will have more time in the next couple of days to put together a more formal test suite.

    3. Neither of those codes returns the ISO-8601 display format used in ANSI/ISO Standard SQL -- you want 126.

    The OP wasn't looking for the ISO-8601 display format. The OP wanted to convert dates currently stored as character strings in dd/mm/yyyy format to the DATE data type. CONVERT can be used for that and using the Date/Time style 103 (or 3) informs the CONVERT function that the date strings provided are in that format.

    4. We want to CAST ( xxx AS DATE) so we can do temporal math with it, so get to the goal in one step, not two.

    Again, not what the OP was requesting. The OP wanted the values in the DATE data type for export to Excel from an SSRS Report.

  • CELKO (8/18/2012)


    CAST (SUBSTRING (IN_STR, 7, 4)+'-'

    +SUBSTRING (IN_STR, 3, 2))+'-'

    +SUBSTRING (IN_STR, 1, 2)

    AS DATE)

    SET DATEFORMAT DMY

    select CAST (SUBSTRING (IN_STR, 7, 4)+'-'

    +SUBSTRING (IN_STR, 3, 2))+'-'

    +SUBSTRING (IN_STR, 1, 2)

    AS DATE)

    FROM DatesTable

    After returning incorrect dates for first 13 lines it gives this back:

    Msg 242, Level 16, State 3, Line 3

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    _____________
    Code for TallyGenerator

  • Sergiy (8/20/2012)


    CELKO (8/18/2012)


    CAST (SUBSTRING (IN_STR, 7, 4)+'-'

    +SUBSTRING (IN_STR, 3, 2))+'-'

    +SUBSTRING (IN_STR, 1, 2)

    AS DATE)

    SET DATEFORMAT DMY

    select CAST (SUBSTRING (IN_STR, 7, 4)+'-'

    +SUBSTRING (IN_STR, 3, 2))+'-'

    +SUBSTRING (IN_STR, 1, 2)

    AS DATE)

    FROM DatesTable

    After returning incorrect dates for first 13 lines it gives this back:

    Msg 242, Level 16, State 3, Line 3

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Okay, Sergiy, you had me nervous for a bit with this one. Then I took a closer look and I saw what the problem was with Mr. Celko's code.

  • CELKO (8/19/2012)


    2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.

    CREATE TABLE #TEMP (

    [DATE] date

    )

    SET NOCOUNT ON

    Go

    DECLARE @InitTime datetime, @I int

    SET @I = 10 -- repeat the operation several times to get an average performance measure

    TRUNCATE TABLE #TEMP

    SET @InitTime = GETDATE ()

    WHILE @I > 0

    BEGIN

    INSERT INTO #TEMP

    SELECT CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE) [Date]

    FROM dbo.Tally AS T

    SET @I = @I -1

    END

    PRINT 'CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

    GO

    DECLARE @InitTime datetime, @I int

    SET @I = 10

    TRUNCATE TABLE #TEMP

    SET @InitTime = GETDATE ()

    WHILE @I > 0

    BEGIN

    INSERT INTO #TEMP

    SELECT CAST (T.ISO_DATE AS DATE) [Date]

    FROM dbo.Tally AS T--, dbo.Tally AS T1

    SET @I = @I -1

    END

    PRINT 'CAST (T.ISO_DATE AS DATE): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

    GO

    DECLARE @InitTime datetime, @I int

    SET @I = 10

    TRUNCATE TABLE #TEMP

    SET @InitTime = GETDATE ()

    WHILE @I > 0

    BEGIN

    INSERT INTO #TEMP

    SELECT CONVERT(date, T.ISO_DATE, 112) [Date]

    FROM dbo.Tally AS T

    SET @I = @I -1

    END

    PRINT 'CONVERT(date, T.ISO_DATE, 112): ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)

    GO

    DROP TABLE #Temp

    Output of 5 consecutive runs on my desktop machine (no other processes are on the way):

    CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:01:007

    CAST (T.ISO_DATE AS DATE): 00:00:00:533

    CONVERT(date, T.ISO_DATE, 112): 00:00:00:527

    CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:730

    CAST (T.ISO_DATE AS DATE): 00:00:00:530

    CONVERT(date, T.ISO_DATE, 112): 00:00:00:527

    CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:767

    CAST (T.ISO_DATE AS DATE): 00:00:00:527

    CONVERT(date, T.ISO_DATE, 112): 00:00:00:530

    CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:703

    CAST (T.ISO_DATE AS DATE): 00:00:00:623

    CONVERT(date, T.ISO_DATE, 112): 00:00:00:527

    CAST (SUBSTRING (T.ISO_DATE, 1, 4) + SUBSTRING (T.ISO_DATE, 5, 2) + SUBSTRING (T.ISO_DATE, 7, 2) AS DATE): 00:00:00:707

    CAST (T.ISO_DATE AS DATE): 00:00:00:560

    CONVERT(date, T.ISO_DATE, 112): 00:00:00:530

    Turns out, SUBSTRING with concatenations is not that fast. It's the slowest option, after all.

    And CONVERT is faster than CAST. Only marginally, but still.

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply