SQL Server date format query - 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

SQL Server date format query

Author

Topic

mike86
Starting Member

24 Posts

Posted - 2007-08-16 : 23:33:55

Dear All,

I'm having a query problem regarding to the date format. From a table, there's a record of patients' birthdate.

In order to identify their age, how should i perform the query?

Select * from patient where BirthDate.Year < 1950

I've tried the above query where i want to extract patients' records who born before 1950, however, it generates error. Can somebody help?

M i K e

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-16 : 23:53:24

What was the error?

mike86
Starting Member

24 Posts

Posted - 2007-08-16 : 23:55:40

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Birthdate.Year" could not be bound.

M i K e

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-17 : 00:02:53

So you just need tear from column Birthdate? Should use datepart function like:

Select * from patient where datepart(yy, Birthdate) < 1950

mike86
Starting Member

24 Posts

Posted - 2007-08-17 : 00:09:48

Brilliant! It works! Thanks rmiao!

M i K e

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-17 : 01:07:26

Select * from patient where Birthdate < '19500101'

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

mike86
Starting Member

24 Posts

Posted - 2007-08-17 : 04:18:30

Hi all, I'm having problems with the below query:

select * from Patient_Rpt where DATEPART(YEAR, Birthdate) IN (SELECT DATEPART(YEAR, Birthdate) from Patient_Rpt)

By right, it should extract all patients' records, however it does not. Since there are some values in birthdate column are null, so it will not retrieve the null values. I've tried to put IS NULL in the parenthesis after the IN, it will generate an error. could somebody help this?

M i K e

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-17 : 05:04:15

select * from patient_rpt
where birthdate < 'Jan 1, 1950'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources