Compare Date value in SQL SP - 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 2000 Forums

Transact-SQL (2000)

Compare Date value in SQL SP

Author

Topic

Dejan
Starting Member

14 Posts

Posted - 2008-06-22 : 12:41:40

I'm using MS SQL Express edition and I have table:

ArticleID
Subject
Body
Date

The Date field have data:
12.02.2005
12.02.1989
12.02.1563
14.09.1345
30.12.345

I need to show Articles wiich have date today. Example:

If today is 12.02.2008 I should show only Date with value 12.02. The results:

12.02.2005
12.02.1989
12.02.1563

independent of year in my date

I try something with DAY, but it's doesn't work.
My code:
SELECT ArticleID, Subject, Body, Date
FROM articles

WHERE Day(getdate())=Left(Date,2)
AND Month(getdate())=RIGHT(Left(Date,5),2)

I recive error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Ju' to data type int.

Help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:58:07

SELECT ArticleID, Subject, Body, Date
FROM articles

WHERE Day(getdate())=DAY(Date)
AND Month(getdate())=Month(Date)

provided your Date field is of datatype datetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-22 : 14:02:17

Do you want to use a present index over the "date" column?
Try this.

select articleid, subject, body, date from articles where
date like REPLACE(CONVERT(CHAR(5), GETDATE(), 3), '/', '.') + '.%'

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

Dejan
Starting Member

14 Posts

Posted - 2008-06-22 : 16:03:15

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources