BETWEEN syntax at SQL

Hi,

I would like to know how MSSQL handles dates for BETWEEN syntax at SQL statements.

e.g. SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

I find that the result rows do not include ones with [date]='2003/12/31'

So, now I'm using BETWEEN '2003/01/01' AND '2004/01/01' instead.

Is it a standard way to perform this kind of task?between ... and ... is equal >= and <=
So I think u can not get the data that date column value is bigger than 2003/12/31 00:00:00

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

must change to ==>

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND dateadd(day,1,'2003/12/31')|||enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps|||Originally posted by TrueCodePoet
enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps

Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'|||Originally posted by Enigma
Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'

That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'|||Originally posted by HanafiH
That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'

Never did I think of it that way ... and believe me .. I have been using it like this for past two years ... thanks for correcting me ... you have been a great help :)

Well .. you learn at least one new thing on this site everyday|||Writing International Transact-SQL Statements (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp)
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language. For more information, see the CONVERT function in the SQL Server Books Online.|||It makes no difference what format the datetime value is sent in. It does make a difference whether the datetime value includes a time portion or not. Many applications cannot restrict themselves to whole date values, and thus some sort of conversion or adjustment is necessary to include all events occuring on the last day.|||Originally posted by buser
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). excellent advice

one minor point: ISO standard does include separators

see Numeric representation of Dates and Time (http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html)|||Sorry.. I might have been confusing .. I will just post what I usualy do.

SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

the two functions should only be run once durring the compile
: )

I have not noticed much difference with this query on 1 mill plus records
Only reasonI don't use the 121 format is the Milleseconds is not needed for me Most of the Time I use the 101 format.

hope this helps|||SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

actually you don't have to convert them

when you put the year first like that, the database will assume that the next field is the month, etc.

SELECT * FROM [sysobjects]
WHERE [crdate]
BETWEEN '2003/01/01 00:00:00'
AND '2003/12/31 23:59:59'