From days to minutes - 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)

From days to minutes

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-21 : 06:26:32

Hi, i'm tryng to adapt an application which shows news, so it has publishdate, expiredate, etc. Now it works on a day to day basis, so it either shows an article on a specific day or it doesn't.
I would like to adapt it so you can also set the hour and minute.

Right now I have:

...
AND
(@Age = -1 OR (HeadlineDate BETWEEN DateAdd(day, @Age, GetDate()) AND GetDate()))
AND
(@IgnorePublishDate = 1 OR IsNull(PublishDate, 1) <= CONVERT(CHAR(8), GETDATE(), 112))
AND
(@IgnoreExpired = 1 OR IsNull(ExpireDate, DateAdd(d, 1, GetDate())) > CONVERT(CHAR(8), GETDATE(), 112))
....

Would I only need to do 121, or is that to simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 07:09:51

@Age is a integer value, probably between 10 and 120.
See this article how to calculate differences in years and months
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 07:10:34

quote:
CONVERT(CHAR(8), GETDATE(), 112)

Why are you converting to string ?

quote:
I would like to adapt it so you can also set the hour and minute.

Not very sure what do you want here but basically you can use dateadd(minute, . . .) or dateadd(hour, . . .) to do it

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 07:14:04

Ahh! Now I see the logical error!

When using the BETWEEN operator, the first value HAS TO BE less than the secnod value!

~~BETWEEN DateAdd(day, @Age, GetDate()) AND GetDate()~~
BETWEEN GetDate() AND DateAdd(day, @Age, GetDate())

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 07:18:30

[code]DECLARE @ThisMinute DATETIME

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT ...
FROM ...
WHERE ...
AND (@Age < 0 OR HeadlineDate >= @ThisMinute AND HeadlineDate < DATEADD(DAY, @Age, @ThisMinute))
AND (@IgnorePublishDate = 1 OR COALESCE(PublishDate, '19000101') <= @Today)
AND (@IgnoreExpired = 1 OR COALESCE(ExpireDate, DATEADD(DAY, 1, @ThisMinute)) > @ThisMinute)[/code]

E 12°55'05.25"
N 56°04'39.16"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-21 : 07:27:31

Looks cool, How did you define @Today?
Can I use GetDate() for that

The secret to creativity is knowing how to hide your sources. (Einstein)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 07:29:43

[code]
declare @today datetime

select @today = dateadd(day, datediff(day, 0, getdate()), 0)[/code]

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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-21 : 07:30:31

Actually Headlinedate should only use the date, and not the time.
Publishdate and Expiredate should use date and time.

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-21 : 07:33:01

So I think I should not use @Today for comparison, but GetDate()

You see, headlinedate is just for showing a date.
Publishdate and Expiredate determine when to show an article, so they should be compared with the accuracy of minutes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 07:49:05

[code]DECLARE @ThisMinute DATETIME,
@ThisDay DATETIME

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101'),
@ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT ...
FROM ...
WHERE ...
AND (@Age < 0 OR HeadlineDate >= @ThisDay AND HeadlineDate < DATEADD(DAY, @Age, @ThisDay))
AND (@IgnorePublishDate = 1 OR COALESCE(PublishDate, '19000101') <= @Today)
AND (@IgnoreExpired = 1 OR COALESCE(ExpireDate, DATEADD(DAY, 1, @ThisMinute)) > @ThisMinute)[/code]

E 12°55'05.25"
N 56°04'39.16"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-21 : 15:27:27

Not exactly what I wanted, but thanks to you, I'm on my way again....
So thanks a lot....

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources