Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query date conversion problem Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2008 12:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2008 1:31 AM
Points: 11, Visits: 31
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 [IP Address], 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 [IP Address], 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
Post #555530
Posted Wednesday, May 29, 2013 11:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 06, 2013 3:48 AM
Points: 1, Visits: 19
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)
Post #1458041
Posted Thursday, May 30, 2013 1:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:55 AM
Points: 105, Visits: 81
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 [IP Address], 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 [IP Address], 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 [IP Address], 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 [IP Address], 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

Post #1458070
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse