Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query date conversion problem


SQL Query date conversion problem

Author
Message
sheetalsh
sheetalsh
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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
samjudson
samjudson
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 28
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
Rasmus Remmer Bielidt
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 141
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search