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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy