How do I retrieve records between a date range?

Question

Post reply

How do I retrieve records between a date range?

Gab Gennai

SSC Enthusiast

Points: 121
More actions
January 19, 2007 at 5:22 pm

#171056

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

DiMac SSC Veteran Points: 215 More actions · Answer 1

You don't have to convert the data. To get all of the records for the 13th and 14th of June, use the following code

where Date >= 13/06/2006 and Date < 15/6/2006

Don't use BETWEEN, it can give incorrect results.

Cheers,

Diane

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

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

Gab Gennai SSC Enthusiast Points: 121 More actions · Answer 3

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

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

Baliram Suryawanshi Valued Member Points: 68 More actions · Answer 5

Hi,

Try with following condition, It will work to solve your problem..

where Datefield > '13 Jun2006' and Datefield < '14 Jun 2006'

Regards,

Baliram

Regards,

Baliram Suryawanshi

dclark SSC Eights! Points: 843 More actions · Answer 6

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.

Gab Gennai SSC Enthusiast Points: 121 More actions · Answer 7

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

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 8

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')

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

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.

Gab Gennai SSC Enthusiast Points: 121 More actions · Answer 10

I have changed the date field from string to datetime and its much easier now.

Thanks to all for their suggestions.