Get Day depending on datetime parameter

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

Get Day depending on datetime parameter

Author

Topic

wll6568
Starting Member

3 Posts

Posted - 2012-01-11 : 08:50:57

I have a stored procedure which takes in a datetime, and what i need is to update a field with it. What I want to do is, if the datetime passed in is before Tuesday 1PM, then update the field with this coming Friday 6AM; if later than Tuesday 1PM, make it next Friday 6AM.

Does anyone know how I can archieve this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 08:53:55

what is your definition of the week ? Sun to Sat or Mon to Sun ? ?

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

wll6568
Starting Member

3 Posts

Posted - 2012-01-11 : 09:04:55

Erm... should be Mon - Sun. If datetime passed in is earlier than Tueday 1PM, then update the field as this Friday, else next Friday

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 09:12:54

I am asking this because, if the date passed is Sunday. What is the expected result ?

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:30:37

What about Wednesday..or Monday...

Wednesday is nect friday?

Monday is this Friday?

What about a date 3 weeks ago?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

wll6568
Starting Member

3 Posts

Posted - 2012-01-11 : 09:36:07

quote:
Originally posted by khtan

I am asking this because, if the date passed is Sunday. What is the expected result ?

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

That will be earlier than Tues, so will update as coming Tuesday.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:52:33

you can mess with this


DECLARE @Last_Monday	 DateTime
DECLARE @Last_Monday_6AM DateTime
DECLARE @Last_Friday_6PM DateTime
DECLARE @day int; SET @day = 1; -- 1 = Monday, 2 = Tuesday, ect

SELECT	@Last_Monday	  = DATEADD(DAY, DATEDIFF(DAY, @day - 1, GetDate()-DATEPART(dw,Getdate())) / 7 * 7, @day - 1)
SELECT	@Last_Monday_6AM  = DATEADD(hh,6,@Last_Monday)
SELECT	@Last_Friday_6PM  = DATEADD(dd,4,DATEADD(hh,18,@Last_Monday))

SELECT @Last_Monday, @Last_Monday_6AM, @Last_Friday_6PM

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-11 : 09:53:06

quote:
Originally posted by wll6568

quote:
Originally posted by khtan

I am asking this because, if the date passed is Sunday. What is the expected result ?

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

That will be earlier than Tues, so will update as coming Tuesday.

Well, Sunday is also after Tuesday ! So what is your cut off day ?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-11 : 10:06:46

I think this is what you want to achieve.

DECLARE	@Now DATETIME = GETDATE()

SELECT	DATEADD(HOUR, DATEDIFF(HOUR, '19000102 13:00:00', @Now) / 168 * 168, '19000112 06:00:00') AS WantedDate

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-11 : 10:12:41

And here is a proper test case

DECLARE	@Sample TABLE
	(
		theTime DATETIME
	)

INSERT	@Sample
VALUES	('20180101 12:55'),
	('20180101 13:05'),
	('20180102 12:55'),
	('20180102 13:05'),
	('20180103 12:55'),
	('20180103 13:05'),
	('20180104 12:55'),
	('20180104 13:05'),
	('20180105 12:55'),
	('20180105 13:05'),
	('20180106 12:55'),
	('20180106 13:05'),
	('20180107 12:55'),
	('20180107 13:05'),
	('20180108 12:55'),
	('20180108 13:05'),
	('20180109 12:55'),
	('20180109 13:05'),
	('20180110 12:55'),
	('20180110 13:05')

SELECT	theTime,
	DATEADD(HOUR, DATEDIFF(HOUR, '19000102 13:00:00', theTime) / 168 * 168, '19000112 06:00:00') AS WantedDate
FROM	@Sample

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 04:56:22

More close today, to proof this method manages month and year changes too.

DECLARE	@Sample TABLE
	(
		theTime DATETIME
	)

INSERT	@Sample
VALUES	('20111226 12:55'),
	('20111226 13:05'),
	('20111227 12:55'),
	('20111227 13:05'),
	('20111228 12:55'),
	('20111228 13:05'),
	('20111229 12:55'),
	('20111229 13:05'),
	('20111230 12:55'),
	('20111230 13:05'),
	('20111231 12:55'),
	('20111231 13:05'),
	('20120101 12:55'),
	('20120101 13:05'),
	('20120102 12:55'),
	('20120102 13:05'),
	('20120103 12:55'),
	('20120103 13:05'),
	('20120104 12:55'),
	('20120104 13:05'),
	('20120105 12:55'),
	('20120105 13:05'),
	('20120106 12:55'),
	('20120106 13:05'),
	('20120107 12:55'),
	('20120107 13:05')
	
SELECT		theTime,
		DATENAME(WEEKDAY, theTime) AS [Weekday],
		DATEADD(HOUR, DATEDIFF(HOUR, '19000102 13:00:00', theTime) / 168 * 168, '19000112 06:00:00') AS WantedDate
FROM		@Sample
ORDER BY	theTime

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-12 : 05:58:59

erm, sure you could do all those inline things or make up functions that do the same, but..... Remember that you are in a database...

so, you could make a static calendar table with two columns called [StartDateTimeRange] and [EndDateTimeRange] (or better names that I can't be bothered to think of now) which map onto the required date (using one of the methods above)

Populate that table with sufficient dates to cover your need for the future (years into the future). (using methods above)

Then create an index over the table that means you can simply select the value from the index.

I'm willing to bet it'll be quicker.

(of course the performance impact is going to be pretty small either way but still)

EDIT : PESO PROVED THIS TO BE TOTALLY WRONG. DON'T DO THIS

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 06:23:30

Sort of this?
(sample data below is not accurate, just a display)


From Time			To time				Wanted time
2011-12-27 13:00:00.000		2012-01-03 13:00:00.000		2012-01-06 06:00:00.000
2012-01-03 13:00:00.000		2012-01-07 13:00:00.000		2012-01-06 06:00:00.000

The problem is that BETWEEN queries (even on clustered index) is not that efficient.

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-12 : 06:50:01

yeah, that's what I meant. With a CI on the [From Time], [To Time] columns.

So do you think that would actually be slower than doing the calculation on the fly?

I'm all up for testing it!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 07:02:13

The design you suggest is very similar to the one Celko is propagating, and I have shown numerous times it's inefficiency (in terms of performance).

But tests are always welcome!

While your tests are running, check out this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162520

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-12 : 07:44:27

I hadn't gotten round to testing yet and it seems that there isn't much point. I concede the point.

I'm pretty surprised is so bad though.

From the execution plan it looks like your suggestion makes great use of parrallism as well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 07:58:41

You can also see here (if you got the time)
http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-tsql-challenge-reoccurring-visits-139751

where I demonstrated that calculating a value is almost always faster then seeking a value.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources