Date Equals Today - 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

Date Equals Today

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2010-01-26 : 05:20:05

Hi
I have a really simple query that I am running in MS Query to return data from a database into Excel. I need to stipulate that we only select data from the current date. I have tried equals NOW() and equals TODAY() but I keep getting the syntax and probably the command incorrect. Can someone kindly help?

SELECT conname.ACCOUNT, conname.FILTER, conname.NEXT_DATE
FROM dbo.conname conname
WHERE (conname.FILTER='SAD')and (conname.NEXT_DATE=TODAY())

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:22:08

GetDate()

but that includes the time. If you jsut want the date (i.e. midnight last night) then:

DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

and, yes, I know that's really ugly! Most efficient (in CPU terms) way though.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:23:59

Actually if conname.NEXT_DATE includes a time component, for "Today" you need:


    conname.NEXT_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) -- Today
AND conname.NEXT_DATE < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)    -- Tomorrow

dibblejon
Starting Member

36 Posts

Posted - 2010-01-26 : 05:30:50

Thanks Kristen

The conname.NEXT_DATE does not include a time component.

Added this code but no data is returned - but the query did not throw an error.

SELECT conname.ACCOUNT, conname.FILTER, conname.NEXT_DATE
FROM dbo.conname conname
WHERE (conname.FILTER='SAD') AND ((conname.NEXT_DATE)>=DATEADD(Day,DATEDIFF(Day,0,GetDate()),0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 06:03:19

are you sure you've data for conditions

WHERE (conname.FILTER='SAD') AND ((conname.NEXT_DATE)>=DATEADD(Day,DATEDIFF(Day,0,GetDate()),0))

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-01-26 : 06:11:48

WHERE DATEDIFF(Day, GetDate(),conname.NEXT_DATE) = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 06:15:50

quote:
Originally posted by NeilG

WHERE DATEDIFF(Day, GetDate(),conname.NEXT_DATE) = 0

wont use any available index on conname.NEXT_DATE field

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 06:56:10

"are you sure you've data for conditions"

Indeed - e.g. does this give you some suitable data?


SELECT TOP 100 conname.NEXT_DATE, conname.FILTER, conname.ACCOUNT
FROM dbo.conname conname
ORDER BY conname.NEXT_DATE DESC,
         CASE WHEN conname.FILTER='SAD' THEN 0 ELSE 1 END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources