SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Display data with todays date		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kpeck
Starting Member

12 Posts
Posted - 10/18/2005 :  10:17:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 10/18/2005 :  10:21:12  Show Profile  Reply with Quote
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

12 Posts
Posted - 10/18/2005 :  10:22:04  Show Profile  Reply with Quote
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

USA
933 Posts
Posted - 10/18/2005 :  11:27:44  Show Profile  Reply with Quote
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

United Kingdom
22191 Posts
Posted - 10/18/2005 :  12:22:17  Show Profile  Reply with Quote
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

12 Posts
Posted - 10/18/2005 :  15:31:00  Show Profile  Reply with Quote
Spot on Kristen, thank you all for your help.

Ken
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000