Date Problem - 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

Transact-SQL (2005)

Date Problem

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 14:39:01

Hello,

I need to query some data and determine if a birthdate falls in between 2 dates based on the birthdate stored in the field date_of_birth. Here is my query:

SELECT cast (a.date_of_birth as datetime) as DOB,
b.create_timestamp, c.service_item_id, c.service_item_desc
FROM person a
JOIN patient_encounter b ON a.person_id = b.person_id
JOIN patient_procedure c ON b.person_id = c.person_id

When I run this the way the a.date_of_birth field is returned is as follows:

DOB:
1975-05-09 00:00:00.000 It is usually stored as a string so this is why i converted to date time.

When I add a Where clause that says Where a.date_of_birth between '1975-05-09' and '1976-05-09' I get no data back but no errors.

Any thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 14:42:21

You'd need to convert a.date_of_birth to datetime in your WHERE clause.

Why don't you just change the data type in the table though so you can do date calculations easier?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 14:42:34

may be this:-

SET DATEFORMAT ymd
SELECT * FROM
(
SELECT cast (a.date_of_birth as datetime) as DOB, 
b.create_timestamp, c.service_item_id, c.service_item_desc
FROM person a
JOIN patient_encounter b ON a.person_id = b.person_id
JOIN patient_procedure c ON b.person_id = c.person_id)t
WHERE t.DOB > '1975-05-09' and t.DOB <='1976-05-09'

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 14:46:00

Its a third party software I cannot change the data types I have to just query them as is.

quote:
Originally posted by tkizer

You'd need to convert a.date_of_birth to datetime in your WHERE clause.

Why don't you just change the data type in the table though so you can do date calculations easier?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 14:47:29

Thanks visakh16 that seems to have worked! :)

quote:

Originally posted by visakh16

may be this:-

SET DATEFORMAT ymd
SELECT * FROM
(
SELECT cast (a.date_of_birth as datetime) as DOB, 
b.create_timestamp, c.service_item_id, c.service_item_desc
FROM person a
JOIN patient_encounter b ON a.person_id = b.person_id
JOIN patient_procedure c ON b.person_id = c.person_id)t
WHERE t.DOB > '1975-05-09' and t.DOB <='1976-05-09'

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-14 : 17:06:42

That set dateformat ymd does not work if I put it into a stored procedure or view. Is this common?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 17:16:48

You can't use SET commands inside views. It should work in a stored procedure though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-14 : 17:26:24

So many bad things

Leave the predicate off

If it's a complex set of business rules I can see that, but a DOB

No

And SELECT *

oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources