Date query for sys_module_users

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 query for sys_module_users

Author

Topic

Sini
Starting Member

18 Posts

Posted - 2007-10-18 : 06:15:55

Hello

I need a query that will extract the start_time for the current day,

So for instance today is the 2007-10-18, i will need all entries for the current date but the start_time cell
contains information like 2007-10-18 09:48:53.000 and any LIKE comparisons i am making are not working.

I would also need this query to work on a daily basis so it could get the current date for tomorrow and the day after etc, so i am thinking a GetDate() would be useful in there somewhere.

Many Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 06:17:57

SELECT * FROM Table1
WHERE Col1 >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
AND Col1 < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')

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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-18 : 06:18:22

umm
select dateadd(dd,datediff(dd,0,getdate()),0)
select dateadd(dd,datediff(dd,0,getdate())+1,0)
select dateadd(dd,datediff(dd,0,getdate())+2,0)

where dateadd(dd,datediff(dd,0,MyDate),0) = dateadd(dd,datediff(dd,0,getdate()),0)
where datediff(dd,mydate,getdate()) = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sini
Starting Member

18 Posts

Posted - 2007-10-18 : 06:21:48

Thanks for the quick replies - i will test them out now.

I had been workign on something crude like this :)

SELECT * FROM sys_module_users
WHERE active = 'Y'
AND start_time <= Getdate()
AND start_time >= Getdate()-1

Sini
Starting Member

18 Posts

Posted - 2007-10-18 : 06:23:45

Thanks Peso that works beautifully, can you explain why you have the
19000101 in there ?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 06:28:46

More than Books Online can provide?

Same reason Nr put 0 there.
It is just a "base date"! It could have been any other date.
It calculated the number of whole days between the "base date" and the current date, thus reducing the time part.
Then I add the number of whole days to the base date again.

Essiantially getting

SELECT *
FROM Table1
WHERE Col1 >= 1 AND Col1 < 2

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

Sini
Starting Member

18 Posts

Posted - 2007-10-18 : 06:33:12

Graciás =]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:38:44

quote:
Originally posted by Peso

SELECT * FROM Table1
WHERE Col1 >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
AND Col1 < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')

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

Any specific reason you used 19000101 and 19000102 instead of 0 and 1?
Just for clarity?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 06:41:49

Clarity.
Books Online says the parameter is a DATETIME. Says nothing about INTEGER.
Most beginners are confused about this.
We seasoned programmers understand that dates are really an offset from a certain point in history.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 07:01:22

Well. I didnt see the previous replies when I posted and thats why my question