Post reply

Not a Valid DateTime Data Type

  • January 17, 2013 at 12:57 pm

    #272909

    Hello Everyone

    This morning I began receiving data thru on of my data pumps that contains an incorrect data type that it not wanting to convert to a valid datetime. I have been looking this for so long now, I have myself confused.

    I queried the entire column using ISDATE() and it returns 0

    The data is exactly like this:

    2012-11-21 16:36:53.850000000

    But yesterday, the same column is this:

    2012-12-19 11:38:00.000

    This data is at first a simple varchar data type because this is the first a few cleansing tables that the data is sent thru.

    How may I convert the data so that I can insert the data into the correct data type columns? I have tried both Convert and Cast, but I am missing something, and I know that it is something simple. But I am blind in one eye and cannot see out of the other.

    Thank You in advance for your help, comments and suggestions

    Andrew SQLDBA

  • January 17, 2013 at 1:19 pm

    #1578456

    The first is a datetime2 format, the second is a datetime format.

    Try CASTing to datetime2 first, then CASTing that to datetime.

    CAST(CAST(column_name AS datetime2) AS datetime)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 17, 2013 at 1:20 pm

    #1578457

    String '2012-11-21 16:36:53.850000000' is too long to convert directly to a datetime value.

    This code shows a couple of ways that work:

    declare @date_str varchar(50)

    set @date_str = '2012-11-21 16:36:53.850000000'

    select ConvertLeft13 = convert(datetime,left(@date_str,23))

    select ConvertThruDatetime2 = convert(datetime,convert(datetime2(7),@date_str))

    select ConvertFull = convert(datetime,@date_str)

    Results:

    ConvertLeft13

    -----------------------

    2012-11-21 16:36:53.850

    (1 row(s) affected)

    ConvertThruDatetime2

    -----------------------

    2012-11-21 16:36:53.850

    (1 row(s) affected)

    ConvertFull

    -----------------------

    Msg 241, Level 16, State 1, Line 10

    Conversion failed when converting date and/or time from character string.

  • January 17, 2013 at 1:29 pm

    #1578463

    Thanks Everyone

    That worked. I have never had a reason to use the DateTime2 datatype, so I did not recognize it.

    Great appreciate your help

    Andrew SQLDBA

Viewing 4 posts - 1 through 3 (of 3 total)

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