Date 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

General SQL Server Forums

New to SQL Server Programming

Date Query

Author

Topic

SQL Red68
Starting Member

8 Posts

Posted - 2011-01-11 : 05:11:18

SELECT DISTINCT PURN FROM dbo.AllData
Where Age >50
AND StartDate ..........????

Hi all,

I need to run a query to select all records the are in February regardless of the year. The StartDate ranges from 01.02.2000 to 10.11.2010

could someone help me to write the correct query please?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-11 : 05:15:26

check this one!

SELECT DISTINCT PURN FROM dbo.AllData
Where Age >50
AND Month(StartDate)=2

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-11 : 05:15:48

use MONTH(StartDate) = 2

sniped..

SQL Red68
Starting Member

8 Posts

Posted - 2011-01-11 : 05:45:24

SELECT DISTINCT PURN FROM dbo.AllData
Where MONTH (StartDate) =2
AND Age >50 or YearBorn >1960

And I got this message:
msg241. Level 16, State1, Line 1
Conversion failed when converting date and/or time from character string.

Can you help?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-11 : 06:07:42

Is startDate a datetime or a varchar?

SQL Red68
Starting Member

8 Posts

Posted - 2011-01-11 : 06:08:20

Varchar I suspect

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-11 : 06:17:45

if varchar then replace

MONTH (StartDate) =2

with

Month(cast(Startdate as datetime))=2

SQL Red68
Starting Member

8 Posts

Posted - 2011-01-11 : 06:21:40

I now get this message:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-11 : 06:49:11

Run:

SELECT StartDate FROM dbo.AllData
WHERE isDate(StartDate) = 0

Then sort out the bad data that is returned before running the top query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 02:32:12

What is the format of dates that are stored in the table?
Always use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources