dates selection - 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

dates selection

Author

Topic

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-24 : 14:58:57

i am having problems selecting by date. I have made sure that the datetime field only contains dates with the time set to 00:00:00

In this instance both it should return one record dated 24/11/2006 00:00:00 but it returns a record with the date 25/11/2006 00:00:00 can someone expain why thi is.

Thanxs

WHERE (Tbl_01020_Tour_Types.TourTypeId = @TourTypeId) AND
(Tbl_01030_Tour_Schedule.Sch_TourStartDate <= CONVERT(DATETIME, CONVERT(INT,GETDATE()))) AND
(Tbl_01030_Tour_Schedule.Sch_TourEndDate >= CONVERT(DATETIME, CONVERT(INT, GETDATE())))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 15:00:57

Because GETDATE() includes time information such as hours, minutes, seconds and thousands of a second.

WHERE Tbl_01020_Tour_Types.TourTypeId = @TourTypeId
AND Tbl_01030_Tour_Schedule.Sch_TourStartDate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND Tbl_01030_Tour_Schedule.Sch_TourEndDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

Peter Larsson
Helsingborg, Sweden

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-24 : 15:08:33

I will try this out this evening thanks for your quick respones.
I was told that
CONVERT(DATETIME, CONVERT(INT,GETDATE()))) would sort this out for me because it returns the dat as dd/mm/yyyy 00:00:00

being new to this can you explain how your way works and do i need to do the same to my fields incase they include thousands of seconds.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 15:10:41

INT does rounding. All GETDATE() after half day will be next day.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-24 : 22:16:26

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-25 : 11:48:30

Any harm in doing it like this.
will this always only return records that = todays date
Field1 >= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0)) AND
field2 <= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0))

can someone explain exactly what this part is actually doing DATEADD(day, DATEDIFF(day, 0, GETDATE()),0))

Also would it be good practice to format the source field like above before doing any calculation on the field ie startdate - 5, would it be best to do it like this just incase there was a time element

DATEADD(day, DATEDIFF(day, 0, StartDate()),0)-5

Thanxs

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-25 : 12:21:41

No harm in doing it like that - that's why Peter suggested it! Understand there are many ways to do this, none is right or wrong as long as you end up with the date or time you want. You can test various versions and some are not efficient, but if a veteran poster like Peter suggests a way, you can pretty much assume its a well tested one

It works like this

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

First (the bold piece) it finds the difference in days between todays date and date zero (which is ~~January 1, 1753~~January 1, 1900 but that really doesn't matter in this case).
Then it adds that many days to date zero which gives a date equal to todays date without any time, so it is always 00:00:00 time, which is what you want to compare to.

For your date minus 5, it will be

DATEADD(day, DATEDIFF(day, 0, Field1) - 5, 0)

which just finds the difference in days between the date in Field1 and date zero.
Then it subtracts 5 from that many days and adds the result to date zero which gives a date equal to 5 days before the date in Field1 without any time.

Look up DATEADD and DATEDIFF in Books Online for more info on those functions.
Note that for this to work we are relying on SQL Server automatically converting an integer into a date for us, which it does quite happily, but that is another topic.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-25 : 12:23:54

quote:
Any harm in doing it like this.
will this always only return records that = todays date
Field1 >= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0)) AND
field2 <= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0))

There is a little harm in doing it exactly like that because you have a typo in this specific code, you have extra ) at the end of each expression, so it should be
Field1 >= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0) AND
field2 <= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0)

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-25 : 12:46:28

what i meannt was that instead of doing it like this
< DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

i change it to
<= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

This seems to get the same result.

Also just to be save should i wrap this format around the source date field to ensure it is in the right format ie no time bits

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 12:47:58

quote:
Originally posted by snSQL

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

First (the bold piece) it finds the difference in days between todays date and date zero (which is January 1, 1753 but that really doesn't matter in this case).

Nitpicking, but date ZERO is actually January 1, 1900.
SELECT CAST(0 AS DATETIME)

quote:
Originally posted by snSQL

For your date minus 5, it will be

DATEADD(day, DATEDIFF(day, 0, Field1) - 5, 0)

DATEADD(day, DATEDIFF(day, 5, Field1), 0)
or
DATEADD(day, DATEDIFF(day, 0, Field1), -5) will be little faster because it needs one less calculation.

Otherwise it was a good explanation.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 12:51:50

quote:
Originally posted by nobby

i change it to
<= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

This seems to get the same result.

The only harm in that is that if you actually have a time with <date + 1> 00:00:00, it would be falsely calculated too.

Keep

mycolumn >= DATEADD(day, DATEDIFF(day, 0, <somedate>), 0)
AND mycolumn < DATEADD(day, DATEDIFF(day, 0, <somedate>), 1)

Because it is logically correct and also uses any index available for faster performance!

Peter Larsson
Helsingborg, Sweden

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-25 : 13:08:49

Field1 >= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0)) AND
field2 <= DATEADD(day, DATEDIFF(day, 0, GETDATE()),0))

sorry being fairly new to sql i don't understand why thiss is a problem , i am using it on 2 different fields in this statememt.

I want to filter field1 to only show anyrecord >= todays date
i then want to add another filter but using field 2 where i only want dates <= todays date if all times are now set to zero why would this cause a problem.

Sorry if this sound thick just trying to understand properly, never had this problem in access because there was a dedicated date only field.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 13:38:26

The statementabove will select all records where
field1 >= 2006-11-25 00:00:00 and field2 <= 2006-11-25 00:00:00

Is that what you want?

should it be

field1 >= 2006-11-25 00:00:00 and field2 < 2006-11-25 00:00:00

Peter Larsson
Helsingborg, Sweden

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-25 : 20:50:34

I have decided to use the statement below because it returns exactly what i need and also plays safe with the source fields ie makes sure the date is as needed.

The purpose of this was to find the current tournament in this case the tournament id is 10000007
then i ask for any start date <=today then any end date >=today
that way if the current tournament started on 21/11/2006 and ends on the 30/11/2006 then it will return this record likewise if it was a 1 day tournament starting today and ending today it would still return the correct record.
Can anyone see any floors in this
as mentioned before fairly new to this so thanks for bearing with me

WHERE (Tbl_01020_Tour_Types.TourTypeId = 10000007) AND
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourStartDate), 0)<= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourEndDate), 0)>= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 04:24:53

Yes, the biggest flaw is that the query now can't use any existing index to speed to the query.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-26 : 13:50:18

quote:
Originally posted by nobby

I have decided to use the statement below because it returns exactly what i need and also plays safe with the source fields ie makes sure the date is as needed.

The purpose of this was to find the current tournament in this case the tournament id is 10000007
then i ask for any start date <=today then any end date >=today
that way if the current tournament started on 21/11/2006 and ends on the 30/11/2006 then it will return this record likewise if it was a 1 day tournament starting today and ending today it would still return the correct record.
Can anyone see any floors in this
as mentioned before fairly new to this so thanks for bearing with me

WHERE (Tbl_01020_Tour_Types.TourTypeId = 10000007) AND
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourStartDate), 0)<= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourEndDate), 0)>= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

This would br a better way to go, because it produces the same results, would use less CPU because you don't have to apply four functions to every row in the table, and it can use an index if there is a suitable one.


WHERE
	Tbl_01020_Tour_Types.TourTypeId = 10000007
 	AND
	Tbl_01030_Tour_Schedule.Sch_TourStartDate <= DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
	AND
	Tbl_01030_Tour_Schedule.Sch_TourEndDate >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

This would also do the job with the same advanatages and less code.


WHERE
	Tbl_01020_Tour_Types.TourTypeId = 10000007
 	AND
	DATEADD(day,DATEDIFF(day,0,GETDATE()),0) between
	Tbl_01030_Tour_Schedule.Sch_TourStartDate AND
	Tbl_01030_Tour_Schedule.Sch_TourEndDate

CODO ERGO SUM

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-26 : 18:17:22

Thankyou
i have tried this one and it works fine
WHERE
Tbl_01020_Tour_Types.TourTypeId = 10000007
AND
DATEADD(day,DATEDIFF(day,0,GETDATE()),0) between
Tbl_01030_Tour_Schedule.Sch_TourStartDate AND
Tbl_01030_Tour_Schedule.Sch_TourEndDate

The only reason i was asking about putting the DATEADD bit around my source data was to find out if anyone thought this would be the sensible thing to do, just incase somehow one of the dates had a time part not = zero.
Even though know one has answered that question i assume by all the code in the replies that maybe this is not a good idea, and if that is the case what happens if some of the source data does have the time aswell won't that effect the results of the query.

Maybe i have misunderstood some where i thought i needed to get all the datetime fields to this format 25/11/20006 00:00:00 to do accurate date filtering on.
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourStartDate), 0)<= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

!!!!!!Slightly confused
But thanxs for all the helpp

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-28 : 01:45:08

Can someone help with the last question?
Then this novice will put this thread to bed.

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 01:52:43

Why should we bother? A lot of people has answered the correct way to do this and yet you persist doing it your own way.
We need some sample data and the expected output based on the provided sample data.

Post this and we might be able yo help you once and for all.

Peter Larsson
Helsingborg, Sweden

nobby
Yak Posting Veteran

58 Posts

Posted - 2006-11-28 : 02:05:24

Hi maybe some misunderstanding here i am not persisting in doing it my way one of the posts (Michael Valentine Jones) said do it like this
WHERE
Tbl_01020_Tour_Types.TourTypeId = 10000007
AND
DATEADD(day,DATEDIFF(day,0,GETDATE()),0) between
Tbl_01030_Tour_Schedule.Sch_TourStartDate AND
Tbl_01030_Tour_Schedule.Sch_TourEndDate

and this is how i am doing it

all i am asking is do i need to ensure the data in the database has a DateTime field in the correct format ie. no time element 28/11/2006 00:00:00.

so do i need to wrap my source data like thi
(DATEADD(day, DATEDIFF(day, 0, Tbl_01030_Tour_Schedule.Sch_TourStartDate), 0) Thats all i am asking.

I don't mean to offend and have only recently changed from access where i didn't have these datetime issues

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 02:11:35

You can do a one time UPDATE to your database.

UPDATE Tbl_01030_Tour_Schedule
SET Sch_TourStartDate = DATEADD(day, DATEDIFF(day, 0, Sch_TourStartDate), 0)

if you want to. Or put this in a trigger and use INSERTED table to do this anytime.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources