SQL Date Query

[CBlackmo]

Hi All,
I have just upsized to a SQL database and must re-write Access queries. I want to create separate queries to find out the list of patients seen yesterday, 2 days ago, 3 days ago, etc. One table includes the date field and the other table includes demographic data. I've tried getdate, adddate to no avail. In Access I used to use Date()-1. This doesn't work in SQL. Please help!

 

[rac2]

In MS SQL Server the functions are
DATEADD(datepart, number, date), add so many dateparts to date
DATEDIFF(datepart, date1, date2), number of dateparts between dates
DATENAME(datepart, date), name of month, day, year, etc
DATEPART(datepart, date), number of the day, month, week, etc
GETDATE(), current system date as a DATETIME value
and datepart={year, quarter, month, day of year, day, week, weekday, hour, minute, second, millisecond}

Books Online comes with SQL Server and is an excellent reference.

Here is an example.

SELECT patients.patient_name, services.date_of_visit
  FROM  patients
  JOIN services ON patients.patient_id = services.patient_id
WHERE services.date_of_visit = DATEADD(day, -1, getdate())

 

[tlbroadbent]

You can also use getdate() - 1. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBlackmo]

I tried the following example and it returned 0 rows. Any suggestions?

SELECT Demographics.Last_Name, Demographics.First_Name, Event_Cath.Cath_Number, Demographics.Patient_ID, Event_Cath.Account_Number, Event_Cath.Date_of_Cath
FROM Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID
WHERE (((Event_Cath.Cath_Number) Like '02%' Or (Event_Cath.Cath_Number) Like '3%') AND ((Event_Cath.Date_of_Cath)=GETDATE()-3))
ORDER BY Demographics.Last_Name, Demographics.First_Name

Thanks for any help you can give me.

 

[tlbroadbent]

It is helpful if I think before answering. Getdate() returns current date and time. The Access Date() function returns only the date. Modify the date comparison as follows. The convert will remove the time.

AND Event_Cath.Date_of_Cath=convert(char(11), GETDATE()-3) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBlackmo]

It worked! Thanks for your help Terry. You are a life saver.

 

[tlbroadbent]

You are welcome! By the way, for future questions about SQL Server and T-SQL use the SQL Server forum - forum183. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.