none
Conversion failed when converting datetime from character string

    Question

  • Hi,

    I am trying to create a report from the SCCM database when I get this error. The problem is that the SCCM table Add_Remove_Programs_DATA uses varchar with NULLS for the Install date (InstallDate00). My problem then is, without changing the data in the table, how can I do a comparision between the column (InstallDate00, varchar(32, NULL) and my date range? Not all rows are null, but some are. Interestingly when I change the table to Add_Remove_Programs_HIST I do not get this error, because there are no NULL values, but I need the current data, not history.

     

    Here is my code:

    Select distinct a.displayname00 as Software, a.installdate00, c.username0 as UserName, c.name0 as Computer

    from Add_Remove_Programs_DATA a inner join Computer_System_data c

    on a.machineid = c.machineid

    where a.InstallDate00 >= DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)

    and a.InstallDate00 < DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    order by c.name0, a.displayname00, c.username0

     

    Thanks for the help!

    Geoff

    Wednesday, December 10, 2008 7:50 PM

Answers

  • Hi Geoff,

    I'm running out of ideas trying to figure out what the problem is. At first, I thought what you meant by null is actually a space, but when you mentioned putting isnull not at the where clause will work fine (filling it with the value you want to substitute), make me really confuse. Couple other things I would like to suggest are:

    1 - possibly there are leading or trailing spaces in your InstallDate field, hence try to trim it. An example would be:
    where convert(datetime,isnull(ltrim(rtrim(a.installdate00)),'20080101')) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    2 - One other thing, since you mentioned that it works fine when the isnull not at the where clause, try something like this (not sure if performance will be affected but we deal with it later if we got the desire result first).

    Select * from
    (
    Select distinct a.displayname00 as Software, isnull(a.installdate00,'22991231') as InstallDate, c.username0 as UserName, c.name0 as Computer
    from
    Add_Remove_Programs_data a inner join Computer_System_data c
    on
    a.machineid = c.machineid
    ) t1
    where t1.InstallDate <> '22991231'
    order by name0, displayname00, username0

    Good luck.

    Glad to help ...
    Thursday, December 18, 2008 5:26 AM
    Answerer
  • FInally got it fixed:

    Select distinct a.displayname00 as Software, a.installdate00 as InstallDate, c.username0 as UserName, c.name0 as Computer
    from Add_Remove_Programs_data a inner join Computer_System_data c
    on a.machineid = c.machineid
    where
    Case
    when
    isdate(InstallDate00)=1 then InstallDate00
    else '20080101'
    end
    between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    and
    DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )
    order by c.name0, a.displayname00, c.username0

    Thanks again for all your help!!!

    • Marked as answer by aGeoff Monday, January 05, 2009 5:38 PM
    Monday, January 05, 2009 5:38 PM

All replies

  • Hi Geoff,

     

    You need to convert the varchar date into datetime. Try this:

     

    Code Snippet

    Select distinct a.displayname00 as Software, a.installdate00, c.username0 as UserName, c.name0 as Computer

    from Add_Remove_Programs_DATA a inner join Computer_System_data c

    on a.machineid = c.machineid

    where convert(datetime,a.InstallDate00,103) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1) and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    order by c.name0, a.displayname00, c.username0

     

     

    Hope that helps. Good luck.

    Wednesday, December 10, 2008 9:07 PM
    Answerer
  • Thank you for your help, Isham. Sorry for the late reply, I was out of the office the last few days. I tried your suggestion but got the following error: 

    Msg 242, Level 16, State 3, Line 1

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

    So far, I have had no luck finding a solution to this. But remember, some of the rows are NULL and I am guessing that is where the problem is.

    Thanks again,
    Geoff

    Tuesday, December 16, 2008 3:30 PM
  • If I do the following:

    Select distinct a.displayname00 as Software, isnull(a.installdate00, '20080101') as InstallDate, c.username0 as UserName, c.name0 as Computer
    from Add_Remove_Programs_data a inner join Computer_System_data c
    on a.machineid = c.machineid
    -- where 'InstallDate' between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    -- and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    order
    by c.name0, a.displayname00, c.username0

    It works Just Fine. But as soon as I do the Where Clause I get the same :
    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.

    Tuesday, December 16, 2008 5:36 PM
  • Hi Geoff,

    Ahhh, null you said. Then try this instead to handle the null. I just give any bogus date, because you gonna drop that row anyway right, if it is null? Let us know if it is still giving problem. Good luck.


    Select distinct a.displayname00 as Software, a.installdate00, c.username0 as UserName, c.name0 as Computer

    from Add_Remove_Programs_DATA a inner join Computer_System_data c

    on a.machineid = c.machineid

    where convert(datetime,isnull(a.InstallDate00,'12/31/2099'),103) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1) and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    order by c.name0, a.displayname00, c.username0

    Also, can you tell me what is your InstallDate date format looks like? The data I mean. Thanks.


    Glad to help ...
    Tuesday, December 16, 2008 5:59 PM
    Answerer
  • Hi Isham,
    The date looks like 20080101. I tried your suggestion before and still get the same error. that is why I move the ISNULL to the select statement. When I check the data it does put in the 20080101 for the entries that are NULL. But when I put the where clause back in, I get the conversion error again.
    Tuesday, December 16, 2008 6:04 PM
  • One other thing, where are you originated? May be you need to remove the style portion (the 103). My quess is, the dateformat is wrong. try just convert(datetime,isnull(a.InstallDate00,'12/31/2099')) between .... This really puzzle me. If US style (mm/dd/yy), use 101. If British style (dd/mm/yy), then use 103. Sorry, I should have pick this thing up at the beginning.
    Glad to help ...
    Tuesday, December 16, 2008 7:54 PM
    Answerer
  • I Originate in the US. I have tried with and without styles. Your style of 103 got me the error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    all other styles, or no style got me the original error: Conversion failed when converting datetime from character string. Like I said above, the ISNULL works great and I can see the Null values change to the date string, but when I add the where clause, it fails. I am just running against Microsoft's own tables from SCCM.

    Thanks again for all your help.
    Wednesday, December 17, 2008 6:59 PM
  • Hi Isham,
    here is the code and results, maybe you can see the problem:

    Select distinct a.displayname00 as Software, isnull(a.installdate00,'20080101') as InstallDate, c.username0 as UserName, c.name0 as Computer
    from Add_Remove_Programs_data a inner join Computer_System_data c
    on a.machineid = c.machineid
    where convert(datetime,isnull(a.installdate00,'20080101')) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )
    order by c.name0, a.displayname00, c.username0

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.

    Select distinct a.displayname00 as Software, isnull(a.installdate00,'20080101') as InstallDate, c.username0 as UserName, c.name0 as Computer
    from Add_Remove_Programs_data a inner join Computer_System_data c
    on a.machineid = c.machineid
    -- where convert(datetime,isnull(a.installdate00,'20080101')) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    -- and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )
    order by c.name0, a.displayname00, c.username0

    Results
    Broadcom Gigabit Integrated Controller      20080118
    Broadcom Gigabit Integrated Controller      20080118
    Configuration Manager Client                   20080118
    Configuration Manager Client                   20080118
    Hotfix for Windows XP (KB915865)            20080118
    Hotfix for Windows XP (KB915865)            20080118
    IBM iSeries Access for Windows                20080101
    IBM iSeries Access for Windows                 20080101
    IBM iSeries Access for Windows SI26879     20080101
    IBM iSeries Access for Windows SI26879     20080101
    Intel(R) Graphics Media Accelerator Driver     20080101
    Intel(R) Graphics Media Accelerator Driver     20080101

    Notice the 20080101 are Null but get filled in witht the date string from the ISNULL. The Only Difference between the two is the inclusion/exclusion of the where clause.

    Also note that if I run the code with the where clause against the HIST table, instead of the DATA table, it does work fine becuase there are no NULL values in the HIST table.

    Wednesday, December 17, 2008 7:14 PM
  • Hi Geoff,

    I'm running out of ideas trying to figure out what the problem is. At first, I thought what you meant by null is actually a space, but when you mentioned putting isnull not at the where clause will work fine (filling it with the value you want to substitute), make me really confuse. Couple other things I would like to suggest are:

    1 - possibly there are leading or trailing spaces in your InstallDate field, hence try to trim it. An example would be:
    where convert(datetime,isnull(ltrim(rtrim(a.installdate00)),'20080101')) between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    and DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )

    2 - One other thing, since you mentioned that it works fine when the isnull not at the where clause, try something like this (not sure if performance will be affected but we deal with it later if we got the desire result first).

    Select * from
    (
    Select distinct a.displayname00 as Software, isnull(a.installdate00,'22991231') as InstallDate, c.username0 as UserName, c.name0 as Computer
    from
    Add_Remove_Programs_data a inner join Computer_System_data c
    on
    a.machineid = c.machineid
    ) t1
    where t1.InstallDate <> '22991231'
    order by name0, displayname00, username0

    Good luck.

    Glad to help ...
    Thursday, December 18, 2008 5:26 AM
    Answerer
  • Hi Isham,

    Sorry, but with the holidays, etc. I wasn't able to follow up with you on this. I have found the problem, finally, now how to test for it:

    there are a some records where the date is different than all the rest. here are some examples:

    2008-05-14 09:50:14
    Fri Aug 17 12:12:37 CDT 2007

    Why Microsoft records different dates is beyond me, but this is what I found searching the table.

    Thanks again, for all your help.
    Geoff

    Monday, January 05, 2009 5:27 PM
  • FInally got it fixed:

    Select distinct a.displayname00 as Software, a.installdate00 as InstallDate, c.username0 as UserName, c.name0 as Computer
    from Add_Remove_Programs_data a inner join Computer_System_data c
    on a.machineid = c.machineid
    where
    Case
    when
    isdate(InstallDate00)=1 then InstallDate00
    else '20080101'
    end
    between DateADD(qq, -1, (getdate() - datepart(dd, getdate()))+1)
    and
    DateAdd(qq,0, (getdate() - datepart(dd, getdate()))+1 )
    order by c.name0, a.displayname00, c.username0

    Thanks again for all your help!!!

    • Marked as answer by aGeoff Monday, January 05, 2009 5:38 PM
    Monday, January 05, 2009 5:38 PM
  • I had this same issue before.  What happens is the compiler tries to convert the data in the where clause first which fails with a conversion error.  By adding the case statement you are forcing it to check if the value is a date first before the conversion.
    Tuesday, January 06, 2009 8:19 PM