SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 HELP WITH BETWEEN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 02/28/2013 :  09:21:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 02/28/2013 :  09:50:15  Show Profile  Reply with Quote
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

USA
439 Posts

Posted - 02/28/2013 :  10:11:12  Show Profile  Reply with Quote
Thanks James...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/28/2013 :  10:18:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000