3

I would like to select all the records in a table between two dates. I have a query like:

SELECT * FROM <table> WHERE (thedate BETWEEN DATE('2012-04-01') AND DATE('2012-06-30'))

This is fine for HSQL, Oracle, PostgreSQL, but it doesn't work in SQL Server due to the lack of the Date function. Is there a way to get this to work generally for all databases (including SQL Server) or do I need to use an ORM like Hibernate (I know I should, but I'm specifically asking if this can be done in SQL)?

dave4351
  • 2,208
  • 2
  • 18
  • 17

2 Answers2

1

There's no need for the Date(...) as far as i can tell. This example seems to work

DECLARE @TheDate Date = '2012-07-01';

SELECT 'hello' WHERE (@TheDate BETWEEN '2012-04-01' AND '2012-06-30')
--None returned
SET @TheDate = '2012-05-01'

SELECT 'hello' WHERE (@TheDate BETWEEN '2012-04-01' AND '2012-06-30')
--selects hello

Edit Btw worth looking at This Question with the date time answer (will post here just to save effort)

The between statement can cause issues with range boundaries for dates as

BETWEEN '01/01/2009' AND '01/31/2009'

is really interpreted as

BETWEEN '01/01/2009 00:00:00' AND '01/31/2009 00:00:00'

so will miss anything that occurred during the day of Jan 31st. In this case, you will have to use:

myDate >= '01/01/2009 00:00:00' AND myDate < '02/01/2009 00:00:00'  --CORRECT!

or

BETWEEN '01/01/2009 00:00:00' AND '01/31/2009 23:59:59' --WRONG! (see update!)

UPDATE: It is entirely possible to have records created within that last second of the day, with a datetime as late as 01/01/2009 23:59:59.997!!

For this reason, the BETWEEN (firstday) AND (lastday 23:59:59) approach is not recommended.

Use the myDate >= (firstday) AND myDate < (Lastday+1) approach instead.

Community
  • 1
  • 1
Manatherin
  • 4,169
  • 5
  • 36
  • 52
0

in sql-server I think you may want to look into the

DATEADD ( datepart , number, date )
DATEDIFF ( datepart , startdate , enddate )

but I think you're looking for

CAST ( expression AS data_type )

or

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

usage:

Select * from myTable where  thedateToCompare >= CONVERT ( datetime , "dd/mm/yy hh:mi:ss:mmmAM" ) and thedateToCompare <= CONVERT ( datetime , "dd/mm/yy hh:mi:ss:mmmAM" )

something that may change your approach is weather your source 'thedate' column type datetime, smalldatetime or is it stored in a column defined as string?

You may want to check out a link! for more detial on datetime constants that you could use for your string variations.

Pareshkumar
  • 176
  • 6