How do I retrieve records between a date range?
-
Hi guys,
I'm new to this and new to SQL Express.
I have a table containing many customer records, including the date they joined. The date column is set as a varchar(20) field.
I need to extract customers who joined using a date range.
I've looked at the Convert method but I'm getting errors:
My syntax is:
SELECT * FROM customers WHERE date >= CONVERT(datetime,'13/06/2006',103) AND date <= CONVERT(datetime,'14/06/2006',103)
The error is:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The date field in the Customer Table is in dd/MM/yyyy format
I've tried many options including:
SELECT * FROM customers WHERE date >= CONVERT(datetime,'13/06/2006',3) AND date <= CONVERT(datetime,'14/06/2006',3)
SELECT * FROM customers WHERE date >= CONVERT(datetime,13/06/2006,3) AND date <= CONVERT(datetime,14/06/2006,3)
SELECT * FROM customers WHERE date >= CONVERT(datetime,'13/06/06',3) AND date <= CONVERT(datetime,'14/06/06',3)
If anyone can help then that would be fantastic.
Thanks
-
the error indicates that it's attempting to use mm/dd/yyyy date format - possibly due to your login set to English rather than british, or dateformat settings
try
where Date >= '06/13/2006' and Date < '06/15/2006'
MVDBA
-
Thanks both Diane and Mike but both of your suggestions failed.
Even if I simply do "...where Date >= '06/13/2006' it returns 22000 records with dates of 19/01/2001 etc.
I've checked my regional settings and the date is in the correct "dd/MM/yyyy" format.
Where to now ? I'm wondering if the issue is spesific to SQL Express ? As mentioned the field is a varchar(40) in the DB table.
GG
-
i think ur data type is datetime try using this
date >= '2006-06-13 00:00:00.000' and date <= '2006-06-14 00:00:00.000'.
between omits the starting and ending days including in the query.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You should use the universal date format for date strings in SQL Server:
YYYYMMDD HH:MM:SS.MIL ( Example: 20061231 23:59:59.997 ).
If you are after data for June 13th and 14th, this is the way to do the query:
Select * from customers Where Date >= '20060613' and Date < '20060615'
Notice that you are asking for greater than or equal to the beginning date, and less than the following date. You can apply the same general query for any range of days. This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.
Notice that the query dates are in format YYYYMMDD; you should always use this format for date strings. This is SQL Servers "universal" date format that works the same with all settings of DATEFIRST. Any other format may produce an error if the setting of DATEFIRST is not exactly what you expect.
-
Thank you to all that replied.
dcclark's suggestion really solved my issue.
All responses sent me into the right direction. The only problem i have now is that I need to change my code to accept the new format. Thats just a tedious process....
Great forum guys
-
You may use "convert" to get a date in the new format.
e.g.
SELECT convert(CHAR(8), getdate(), 112)
(gives 20070122)
When you want the date quoted then you could use:
SELECT quotename(convert(CHAR(8), getdate(), 112),'''')
(gives '20070122')
-
You might consider storing date values as datetime or smalldatetime, instead of as strings. It would avoid problems like this, as well as save some space in the database.
-
I have changed the date field from string to datetime and its much easier now.
Thanks to all for their suggestions.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply