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'

between not betweening inclusive

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Time also has a milliseconds value. It is quite likely that your time values are in actuallity greater than exactly 16:00:00 -perhaps 16:00:00.001.

If you wish to include the times that are between 16:00:00 and 16:00:01 (meaning all times with any millisecond value, then use 16:00:01.

If you wish to ignore the milliseconds value, you may be better served using the smalldatetime datatype.

|||

rwbogosian wrote:

In the sql statements below, both BETWEEN and (>= and <=) are giving unexpected results. I am working with monthly data where in one table the time part of [DateTime]=09:31:00 the result is correct but if the time is 16:00:00 (in another table) the statement returns empty. Also if I change either sql statement to use 16:01:00, it returns the second table correctly but that should not be necessary because both statements below are supposed to be inclusive. Anybody know why this is happening and what to do about it?

select distinct * from [clean].[table2] where

convert(varchar, [DateTime],126) between '2005-11-30' and '2005-12-01' and

convert(varchar, [DateTime],114) between '09:30:00' and '16:00:00'

order by [DateTime]

The following statement has the same problem:

select distinct * from [clean].[table2] where

(convert(varchar,[DateTime],126) >= '2006-06-05' and

convert(varchar,[DateTime],126) <= '2006-11-05') and

(convert(varchar,[DateTime],114) >= '09:30:00' and

convert(varchar,[DateTime],114) <= '16:00:00')

order by [DateTime]

Why cant you just use

SELECT DISTINCT
*
FROM
clean.table2
WHERE
DateTime >= '2006-06-05 09:30:00' AND DateTime <= '2006-11-05 16:00:00'
|||Your query is doing string compares, which never work with between.

You need to do this:

SELECT DISTINCT * FROM [clean].[table2] WHERE
[DateTime] between CAST('2005-11-30' AS DATETIME) AND CAST('2005-12-01' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar,[DateTime],114)) BETWEEN CONVERT(datetime, '09:30:00') AND CONVERT(datetime, '16:00:00')

This will convert the dates to 1900-01-01 and compare the times correctly.

There is no reason to convert the dates to strings.|||

Changing the statement to use 16:00:01 does work. The actual value in the table is 16:00:00.000 but not even 16:00:00.9999 works; it has to be a full second after the actual time in the database table.

I know in the near future I'm going to have problems when we start analyzing even smaller time frames. While I am happy that changing one second works, shouldn't BETWEEN and (<= or >=) be inclusive. Is this a bug or a feature?

|||Can't use your statement because I can't use anything before 09:30:00 or after 16:00:00 on any of the dates between (inclusive).|||Thanks Tom; that appears to be working.

Best way to select a Constant String from a Table ?

Hi,
What is the best way to generate a constant String from a Table (Only 1 row)
.
I have some sql statements returning a few rows. I want to also return a row
with a Constant String along with this sql statement..
e.g.
Select * from orders
Union
Select 'End of Order Select' from '
I can try selecting it from orders table, but this should return only one ro
w.
Any ideas ?
- AnandSorry for the trouble, found out the answer that I can just do
Select 'End of Order Select'
"S Anand" wrote:

> Hi,
> What is the best way to generate a constant String from a Table (Only 1 ro
w).
> I have some sql statements returning a few rows. I want to also return a r
ow
> with a Constant String along with this sql statement..
> e.g.
> Select * from orders
> Union
> Select 'End of Order Select' from '
> I can try selecting it from orders table, but this should return only one
row.
> Any ideas ?
> --
> - Anand|||hi Anand
This will work fine if you are note selecting any value from a table. If u
require a value
from a table along with a constant value, then u require to do like this:
SELECT TOP 1 'Const Value', <COLUMNS> FROM <TABLE>
you query can be modified as
Select * from orders
Union ALL
Select 'End of Order Select'
try using UNION ALL if u definately want the text to be displayed.
in case of UNION the second table will not display a value if the same value
exists
in the main table.
Hope this gives u a better picture
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"S Anand" wrote:
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>|||A few comments:

> Select 'End of Order Select'
Use square brackets of double-quotes around the produced column name instead
. The column you produce
in the result is an identifier and non-standard identifiers are delimited wi
th double-quotes in SNAI
SQL (and SQL Server) and SQL Server also allow double-quotes. Why SQL Server
allow single quotes for
identifiers in this particular case is beyond my understanding, very strange
..
Don't do SELECT *. It might just have been an example, but imagine of the ta
ble structure changes
and you add or remove columns. The UNION won't work.
Also, don't expect the SELECT with a constant to come last unless you do an
ORDER BY for the UNION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Anand" <x@.hotmail.com> wrote in message
news:81178FBE-0B61-411E-8836-D01240F81227@.microsoft.com...
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>