Date Less Then or Equal - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date Less Then or Equal

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2010-01-21 : 21:27:28

I have a script which works other then it does not report on the date loaded as the end date.

use Reporting
Select CLientid, Account, Transcode, Narrative, [Debit/credit],
Transamount, convert(varchar(10),Transdatetime,103) as TranDate, convert(varchar(10),TransEffectiveDate,103) as EffectiveDate
from dbo.AccTran
inner join Manual_Trancodes
on transcode = trancode
where (transamount is not Null) and (transamount >= @MinAmt) and (transamount <= @MaxAmt) and (transdatetime >= @StartDate) and (transdatetime <= @EndDate)
order by Transdatetime desc
If I end Start Date of the 20 Jan and End Date of the 21 Jan I only get the records for the 20 Jan. If I load 20 Jan and 22 Jan I get records for 20th and 21st.

I am stumped. Can anyone explain what is wrong?

One solution that works is to change to (transdatetime -1 < @Enddate) but it is not the right way to do it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-21 : 22:10:19

Your solution of subtracting one day is actually correct.

Variables of DATETIME data type contain both date and time information. If you provide only the date part, SQL sets the time part as the beginning of the day, which is midnight. This means

transdatetime <= '2010-01-21'

is interpreted as "transdatetime less than or equal to midnight of the 21st". To make it work the way you expect, you can add one day (or subtract one day from the other side as you did)

transdatetime < dateadd(day,1,@EndDate)

Be sure to use < instead of <= when you do this, or you will pick up data for midnight of the next day.

SQL 2005 and SQL 2000 have only two types for storing date and time - datetime and smalldatetime. Both these types store date part and time part whether you want it or not. SQL 2008 is more flexible - there are data types for storing only date, or only time etc.

Les.61
Starting Member

49 Posts

Posted - 2010-01-26 : 19:40:39

Many thanks for the information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources