Query that returns data for 'today' -evryday

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 2000 Forums

Transact-SQL (2000)

Query that returns data for 'today' -evryday

Author

Topic

pabster
Starting Member

2 Posts

Posted - 2007-10-23 : 07:03:57

Hi,

I am looking to pull data from a table or tables specifying that the criteria is for today. I need this to run every day without manually changing the date. Is there something like GETDATE that would specify that the criteria means 'today' even if it runs everyday.

something like this: select fname, sname, orderid from tblorders where orderdate = today

any help would be welcome,

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 07:07:53

WHERE OrderDate >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
AND OrderDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)

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

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 07:13:09

pedantic point:

I prefer to see

DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)+1, 0)

instead of ", 1)" as final parameter because "0" as final parameter will be valid for all units of DATEDIFF, where "1" as final parameter is only valid for "day" - and is really just a side effect ...

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:41:25

Actually K's style will be more generic, like to find the next 1st of next MONTH

DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

Maybe for nicer alignment of the code, this is used

WHERE OrderDate >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
AND   OrderDate <  DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)

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

pabster
Starting Member

2 Posts

Posted - 2007-10-23 : 07:44:46

Hi and thanks -it works great.
Next what I need to do is output the results to a text file automatically. Can you assist?

thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:46:20

see http://mindsdoor.net/SQLTsql/WriteTextFile.html

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:26:48

quote:
Originally posted by khtan

Actually K's style will be more generic, like to find the next 1st of next MONTH

DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

Really?

SELECT	DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 08:40:53

and also make sure DATEDIFF precedes first parameter of DATEADD

SELECT   DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0),
	DATEADD(MONTH, 0,DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 09:40:16

"SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0)"

Too obtuse IMHO. We don't code like that anyway!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources