Simple query - out-of-range Error

1

Hi,

I have a table 'policy' with [DateCreated] [datetime] NOT NULL column and I tried to run a simple select statement

select datecreated from Policy where (policy.DateCreated BETWEEN '2015-04-17 00:00:00.000' AND '2015-04-17 23:59:59.997')

I am getting the below error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

However Table has valid data [dateformat in datetime column : 2015-04-17 09:08:50.250]

Can anyone please tell me why i am getting this error message?

Thanks

Regards,
Satheesh

2

I find using the YYYYMMDD format usually causes less issues. Does the following code work?

SELECT	P.DateCreated
FROM	Policy AS P
WHERE	P.DateCreated >= '20150417'
		AND P.DateCreated < '20150418';
1 Like
3

Thank You Dohsan. Its working.

4

You may need to read this to understand why you need to always use unambiguous datetime format http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

2 Likes
5

Thank you for sharing this link Madhivanan. Very usefull.

Thanks

1 Like