SQL Query date conversion problem

  • sheetalsh

    SSC-Addicted

    Points: 439

    Hi,

    The query below is working fine for the date as 04/08/2008 and 08/08/2008, however it is not working for the date 11/08/2008 and 15/08/2008.

    It is because it considering 15th as a month so I tried to put date as 08/11/2008 and 08/15/2008 also as 2008/11/08 and 2008/15/08, but still it is giving me error as

    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.

    Even I set "set dateformat dmy", but still an error is coming.

    This is the query:

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn where visited_on between convert(datetime,convert(varchar,'11/08/2008',101)) and convert(datetime,convert(varchar,'15/08/2008',101))

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

    union

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn where visited_on between convert(datetime,convert(varchar,'11/08/2008',101)) and convert(datetime,convert(varchar,'15/08/2008',101))

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

  • samjudson

    SSC Enthusiast

    Points: 139

    Not sure why you are trying to convert a string to a string and then to a date in the second part.

    But 103 is UK format, not 101. Try passing this into the datetime convert, not the varchar one.

    CONVERT(datetime, '18/05/2013', 103)

  • Rasmus Remmer Bielidt

    SSCommitted

    Points: 1746

    sheetalsh (8/20/2008)


    Hi,

    The query below is working fine for the date as 04/08/2008 and 08/08/2008, however it is not working for the date 11/08/2008 and 15/08/2008.

    It is because it considering 15th as a month so I tried to put date as 08/11/2008 and 08/15/2008 also as 2008/11/08 and 2008/15/08, but still it is giving me error as

    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.

    Even I set "set dateformat dmy", but still an error is coming.

    This is the query:

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn where visited_on between convert(datetime,convert(varchar,'11/08/2008',101)) and convert(datetime,convert(varchar,'15/08/2008',101))

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

    union

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn where visited_on between convert(datetime,convert(varchar,'11/08/2008',101)) and convert(datetime,convert(varchar,'15/08/2008',101))

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

    It is not working fine for 04/08/2008 (August 4, 2008), you just dont get an error since it is a valid date, but is actually interpreted as April 8, 2008.

    I would rely on an implicit conversion (I know, bad karma) to convert a string to date since it works intuitively. If you specify dates in a string as the format 'YYYYMMDD' it always converts correctly in my experience.

    I am a bit confused about your two converts in the select and group by - why?

    -- untested

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn

    where visited_on between '20080811' and '20080815' -- relies on implicit conversion, YYYYMMDD have always worked for me

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

    union

    select user_id as EIN, machine_ip as 172.16.23.120, location_code as [Location Code], OUC,CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)) as date

    from btblAuditlog_trn

    where visited_on between '20080811' and '20080815'

    group by CONVERT(DATETIME,convert(VARCHAR,VISITED_ON ,101)),menu_item_id,user_id,machine_ip,location_code,ouc

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

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