CODEWITHSUNDEEP
c#sql-server-2008
ghanshyam.mirani
ghanshyam.mirani

Reputation: 3101

Issue Related to Date Range in SQL Server

I have written following Query in SQL Server :

SELECT * 
FROM AttendanceMaster 
WHERE Date between '8/12/2012' AND '8/20/2012'

butt Query result returns '8/13/2012' AND '8/19/2012'

but it doesn't returns result for date 12/8/2012 and for date 8/20/2012

so what is the solution to get the data for above two date ?

Upvotes: 0

Views: 280

Answers (5)

Krishna Thota
Krishna Thota

Reputation: 7026

If you give dates '2012-08-12' and '2012-08-20' then it returns the data between '2012-08-12 0:00 AM' and '2012-08-20 0:00 AM' So it does not return the valuesfrom the date '2012-08-12'. So you need to write the target date as '2012-08-21'

If you have the date '2012-08-20' in a variable for ex:

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SET @FromDate = '2012-08-12'
SET @ToDate = '2012-08-20'

DECLARE @NextDate DATETIME

SET @NextDate=CAST(FLOOR(CAST(dateadd(day,1,@ToDate) AS FLOAT)) AS DATETIME)
/*This Returns the Next Day Date.*/

SELECT * 
FROM AttendanceMaster 
WHERE Date between @Date AND @NextDate

Or you can write as

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SET @FromDate = '2012-08-12'
SET @ToDate = '2012-08-20'

SELECT * 
FROM AttendanceMaster 
WHERE Date between @Date AND CAST(FLOOR(CAST(dateadd(day,1,@ToDate) AS FLOAT)) AS DATETIME)

Upvotes: 0

Saurabh R S
Saurabh R S

Reputation: 3177

I would suggest you to use <, <=, >=, or > because they allow more flexibility than BETWEEN and you have the choice to including or exclude the endpoints. So you can rewrite your query like this 

SELECT * 
FROM AttendanceMaster 
WHERE Date > '8/12/2012 00:00:00' AND Date < '8/21/2012 00:00:00'

Plz note that in the above query the end date is 21st and not 20th.

Also remember that by default SQL Server will take any date like '8/20/2012' AS '8/20/2012 00:00:00' and therefore any datetime greater than this will not appear in result if you use BETWEEN or if you dont specify the exact time stamp.
Hope it helps.

Upvotes: 0

Akash KC
Akash KC

Reputation: 16310

Try with following query:

SELECT * 
FROM AttendanceMaster 
WHERE CAST(Date AS DATETIME) 
                   BETWEEN DATEADD(DAY , -1 ,CAST('2012-08-12' AS DATETIME)) AND 
                           DATEADD( DAY , 1 ,CAST('2012-08-20' AS DATETIME));

Upvotes: 1

John Woo
John Woo

Reputation: 263733

its not TO but instead AND

select * 
from AttendanceMaster 
where Date between '8/12/2012' AND '8/20/2012'

try casting it to date

SELECT * 
FROM   AttendanceMaster 
WHERE  CAST([Date] AS DATE) BETWEEN CAST('2012-08-12' AS DATE) AND CAST('2012-08-20' AS DATE)

Upvotes: 0

John Gathogo
John Gathogo

Reputation: 4655

If you are using SQL Server 2008, you only need to do something like this:

SELECT * 
FROM   AttendanceMaster 
WHERE  CONVERT(DATE, [Date]) BETWEEN CAST('2012-08-12' AS DATE) AND CAST('2012-08-20' AS DATE)

EDIT: I am making 2 assumptions here.

1) That you have indeed confirmed that you have data in your AttendanceMaster table that has [Date] equal to 12th and/or 20th August 2012 - you dont want to look for something that is not even there in the first place ;)

2) That the [Date] column is most probably of DATETIME SQL Server type

Upvotes: 0

Related Questions