Comparing today's date with date in field

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

Comparing today's date with date in field

Author

Topic

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 14:27:52

I want to be able to compare today's date with the date that is in the database. Right now I have:

Select Field1, Field2
FROM table 1
Where Year(TS_Date)=Year('3/1/2006')and Month(TS_Date)=Month('3/1/2006')

Where I have to change the date every month. Is there a way to use GetDate or another type of code so it could automatically update every month.
Any suggestions would be very greatful.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 14:29:52

Is TS_Date using datetime data type? Show us some of the data.

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

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 14:35:15

some of the data is 1/1/2008 2/2/2008 3/1/2008 4/1/2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 14:43:29

But what is the data type? varchar/char or datetime?

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

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 14:50:59

datetime

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 14:56:05

Will TS_Date have time data in it or just 00:00?

You can use this to get 00:00 out of GETDATE and other columns:
DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

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

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 15:03:19

TS_Date does not have time data, I just want to compare the month and year in the database with the current month and year

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:22:42

select * from table1
where ts_date >= '20080501' and ts_date < '20080601'

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 15:25:06

I think the question was how not to hard code the values but rather use GETDATE().

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:30:21

declare @monthstart datetime, @monthend datetime
select @monthstart = dateadd(month, datediff(month, '19000101', getdate()), '19000101'),
@monthend = dateadd(month, datediff(month, '18991231', getdate()), '19000101')

select * from table1
where ts_date >= @monthstart and ts_date < @monthend

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

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 15:55:33

is this the only way I could the data that I want

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 15:56:45

It's the most efficient. Do you want a slower approach?

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

jept
Starting Member

14 Posts

Posted - 2008-05-19 : 16:03:21

yah I'll try a slower approach.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 16:13:20

Lol. I refuse to post a query that I know is inefficient.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources