August 20, 2008 at 12:46 am
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 127.0.0.1, 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 127.0.0.1, 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
May 29, 2013 at 11:51 pm
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)
May 30, 2013 at 1:14 am
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 127.0.0.1, 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 127.0.0.1, 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 127.0.0.1, 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 127.0.0.1, 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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply