Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 HELP WITH BETWEEN

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-02-28 : 09:21:20
I am trying to use BETWEEN to return records for a specific day.

I have no issues when the two date values are different.
My issue is when using a date of the same value.

What I want to do is append 00:00:00.000 to the start date, and
99:99:99.999 to the end date.

This sample sql code reads in two date values from an external application. It is possible that someone will want to return all records for a single day.

Hope this makes sense...


declare @a char(10) = null
declare @b char(10) = null
set @a = '2/27/2013'
set @b = '2/27/2013'

select * from MYTABLE dsh
where dsh.aDate between convert(datetime, @a, 121) and convert(datetime, @b, 121)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 09:50:15
There are pitfalls in using BETWEEN for date queries. The preferred and most reliable method is to use a >= and < as shown below:
declare @a DATETIME = null
declare @b DATETIME = null
set @a = '20130227'
set @b = '20130227'

select * from MYTABLE dsh
where dsh.aDate >= @a AND dsh.aDate < DATEADD(dd,1,@b);
You will notice that I made several changes to your query - the data types of @a and @b, the string literal for dates using YYYYMMDD format, and changing the where clause as I described above.
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-02-28 : 10:11:12
Thanks James...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 10:18:15
see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -