make end date inclusive - 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

SQL Server 2005 Forums

Transact-SQL (2005)

make end date inclusive

Author

Topic

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-08 : 07:06:13

I have a query that is suppose to return data for specified Startdate and Enddate ...the results don't include Enddate...how can make Enddate inclusive...i also tried using between Startdate and Enddate..still not working.

SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate

FROM Cetus.dbo.JMPackSlipHeader as JMPSH

INNER JOIN Cetus.dbo.HHContainerType as HHCT ON

JMPSH.fkContainerType = HHCT.pkContainerTypeID

Where JMPSH.CreateDate >='2009-06-01' AND JMPSH.CreateDate <='2009-06-05'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 07:15:44

your column CreateDate must have contain the time. Change the where to


Where JMPSH.CreateDate >='2009-06-01' AND JMPSH.CreateDate < '2009-06-06'

KH
[spoiler]Time is always against us[/spoiler]

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-08 : 08:36:55

quote:
Originally posted by khtan

your column CreateDate must have contain the time. Change the where to

Where JMPSH.CreateDate >='2009-06-01' AND JMPSH.CreateDate < '2009-06-06'

KH
[spoiler]Time is always against us[/spoiler]

Hi khtan
it's not working ..i want to show data for the end date that it's entered....if end date it's 2009-06-06 it must also include data for 2009-06-06..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 08:43:42

i mean if your date range is 2009-06-01 to 2009-06-05
check for


CreateDate >= '2009-06-01'
and CheckDate < '2009-06-06' -- add 1 day to your end date

or you can use this


and CheckDate < dateadd(day, 1, '2009-06-05')

KH
[spoiler]Time is always against us[/spoiler]

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-07-09 : 15:01:40

Your other option is to append the last possible time of day to the end date value. If the field is a datetime, then that time would be 23:59:59.997.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 15:25:01

And remember to change the hardwired time part when upgrading to SQL Server 2008.

N 56°04'39.26"
E 12°55'05.63"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-07-09 : 15:30:27

Peso, please elaborate. I thought that a DateTime field in SQL 2008 was the same as one in SQL 2005. I know there are other date and time data types in SQL 2008, but the upgrade process doesn't automatically convert your database datatypes...does it?

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 15:39:42

Yes, the DATETIME datatype has the same resolution.
But what if during the upgrade process someone decides to change the column datatype to DATETIME2, where you can have a resolution of 100 nanoseconds?

The open-ended search criteria is and always will be the best choice for selecting a time range. It guarantees that the query will work no matter the resolution is used.

I agree there is a slim chance that someone enters a record in the small time range of
23:59:59.997 and 23:59:59.9999999, but still...

It gets even worse if someone decides to change to the DATE datatype during the upgrade.

DECLARE	@dt DATETIME

SET	@dt = '20090709 23:59:59.997'

SELECT	@dt,
	CAST(@dt AS DATETIME2(7)),
	CAST(@dt AS DATE)

Depending on how you write your query, the column used in WHERE clause can be victim of implicit conversion. Or the hardwired part can be implicit converted to fit the column's datatype, depending on how the query is written.

N 56°04'39.26"
E 12°55'05.63"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-07-09 : 16:44:46

OK, you have a good point there, but to be precise, it is not the act of upgrading to 2008 that requires the change you speak of, but the act of changing the data type. I would also argue that you should never be in a situation where just anyone can decide to change datatypes in the middle of an upgrade, and you should have tools that let you review the usage of database fields when you do decide to alter the schema. And if we're going to go down the path of best practices, I'm sure you would also agree that it would actually be better to explicitly convert the values you want to use in your search into the appropriate matching data type as the column and not rely on the implicit conversion from a character type.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 16:57:49

You're correct. It's not the upgrade process that make the existing code vulnerable to changes.
It is, as you point out, someone's decision to change the column datatype.

Using the ".997" approach have some caveats, such as the developer must document where he or she has used the ".997" method. In order to later know all places where this should be changed.

Using the "Day after" method doesn't need this. It will continue to work time after time...

One have to remember that a datetime value is nothing more than a "serial number" starting with 0 for SQL Server and the day of January 1st 1900.
The integer part is the number of days, and the fractional part is the part within any given day.

So it's pure math using the open-ended search criteria.

from <= datetime < to

Now it doesn't matter what the fractional part is, no matter how many decimals, because next day (0 in fractional part) will always be larger than any given fractional part from the day before.

And my personal experience is that documenting is not a common habit of database developers.

N 56°04'39.26"
E 12°55'05.63"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-07-09 : 17:47:59

Much agreed, Peso. Especially your last sentence which is why I rely on tools to do DIFFs, searches, and object/owner identification to find out what's really there, not just what was documented or the developer can remember.

Cheers!

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 21:20:41

now i see why peso always made me <= datetime <...learnt something from here...thx alot

Hope can help...but advise to wait pros with confirmation...

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-10 : 03:55:07

quote:
Originally posted by khtan

i mean if your date range is 2009-06-01 to 2009-06-05
check for

CreateDate >= '2009-06-01'
and CheckDate < '2009-06-06' -- add 1 day to your end date

or you can use this

and CheckDate < dateadd(day, 1, '2009-06-05')

KH
[spoiler]Time is always against us[/spoiler]

thanks khtan i used
CheckDate < dateadd(day, 1, '2009-06-05')
and it's working it includes the enddate entered....:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources