Sql Server Programming :: Datetime conversion


	Datetime conversion I am trying to pull a list of customers that did not place an order during April 2000. When I run my query I get back the following message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Why? SELECT C.CustomerID,C.CustomerName FROM Customers C WHERE NOT Exists (SELECT * from Orders O WHERE O.CustomerID=C.CustomerID and O.OrderDate between '4-01-00' and '4-31-00') Hello, Ruth You should specify date constants in a format that is not ambiguous, for example yyyymmdd. Also, April has only 30 days, not 31. In your case, try: SELECT C.CustomerID,C.CustomerName FROM Customers C WHERE NOT Exists (SELECT * from Orders O WHERE O.CustomerID=C.CustomerID and O.OrderDate between '20000401' and '20000430') Razvan -----------------------------------------------Reply----------------------------------------------- (a) April only has 30 days! (b) use ranges (not BETWEEN) and use YYYYMMDD instead of potentially ambiguous formats. ... AND o.OrderDate >= '20000401' AND o.OrderDate < '20000501' -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 "RuthE" <R@discussions.microsoft.com> wrote in message news:64A1F610-BEED-420E-BE73-830501D2786C@microsoft.com... - Hide quoted text - >I am trying to pull a list of customers that did not place an order during > April 2000. > When I run my query I get back the following message: The conversion of a > char data type to a datetime data type resulted in an out-of-range > datetime > value. Why? > SELECT C.CustomerID,C.CustomerName > FROM Customers C > WHERE NOT Exists > (SELECT * from Orders O > WHERE O.CustomerID=C.CustomerID and > O.OrderDate between '4-01-00' and '4-31-00') On Jun 5, 6:58 pm, RuthE <R@discussions.microsoft.com> wrote: > I am trying to pull a list of customers that did not place an order during > April 2000. > When I run my query I get back the following message: The conversion of a > char data type to a datetime data type resulted in an out-of-range datetime > value. Why? > SELECT C.CustomerID,C.CustomerName > FROM Customers C > WHERE NOT Exists > (SELECT * from Orders O > WHERE O.CustomerID=C.CustomerID and > O.OrderDate between '4-01-00' and '4-31-00') Unfortunately April has only 30 days :-) Regards Amish Shah http://shahamishm.tripod.com -----------------------------------------------Reply----------------------------------------------- > SELECT C.CustomerID,C.CustomerName > FROM Customers C > WHERE NOT Exists > (SELECT * from Orders O > WHERE O.CustomerID=C.CustomerID and > O.OrderDate between '20000401' and '20000430') Note that if OrderDate includes time information and not just the order date at midnight, this will likely miss most or all of the rows from the last day of the month. This is why I always recommend against using BETWEEN for date ranges unless you are confident that all dates are rounded to midnight. A