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)
 Display data with todays date

Author  Topic 

kpeck
Starting Member

13 Posts

Posted - 2005-10-18 : 10:17:03
I have a table that has a date field that is populated by default with (getdate()). I have a view that I want to only pull out records with todays date, however even though there are several records in the table with todays date the view returns no records. The view criteria I'm using is = DATEADD([day], DATEDIFF([day], 0, GETDATE()), 0). I'm thinking the issue may be that the field is actually populated with a date time stamp and not just a short date.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 10:21:12
You formula will truncate the datetime value to a whole date, which I assume is what you want. Please post the code for your view.
Go to Top of Page

kpeck
Starting Member

13 Posts

Posted - 2005-10-18 : 10:22:04
SELECT [Last Name], [First Name], [Middle Initial], SS#, [Dept #]
FROM dbo.[Nurse Orientation - tbl]
WHERE (CurDate = DATEADD([day], DATEDIFF([day], 0, GETDATE()), 0))
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-18 : 11:27:44
I assume curDate is a datetime? If so, apply the same logic to the column:

where (DATEADD([day], DATEDIFF([day], 0, CurDate), 0)= DATEADD([day], DATEDIFF([day], 0, GETDATE()), 0))

Nathan Skerl
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 12:22:17
You need to check that the CurDate is in a range from midnight last night up to, but not including, midnight tonight. i.e.

SELECT [LastNight] = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0),
[Tonight] = DATEADD(day, DATEDIFF(day, 0, GETDATE())+1, 0)

thus you should be able to find the relevant rows with:

SELECT [Last Name], [First Name], [Middle Initial], SS#, [Dept #]
FROM dbo.[Nurse Orientation - tbl]
WHERE CurDate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND CurDate < DATEADD(day, DATEDIFF(day, 0, GETDATE())+1, 0)

Kristen
Go to Top of Page

kpeck
Starting Member

13 Posts

Posted - 2005-10-18 : 15:31:00
Spot on Kristen, thank you all for your help.

Ken
Go to Top of Page
   

- Advertisement -