date comparison help - 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)

date comparison help

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2005-01-31 : 03:48:07

how to compare 2 dates but taking into consideration only the year and the month.
ex. i have table t with test_date as column
i want to : select * from t where t.test_date < getDate()

but the comparison must be on month and year. For example t.test_date=2004/1 to be compared with 2005/1

How's that?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-31 : 04:44:06

where month(t.test_date) = month(getDate()) and year(t.test_date) = year(getDate())

Go with the flow & have fun! Else fight the flow

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-31 : 06:02:26

try this...

select * from t where 
replace(convert(varchar(7), t.test_date, 111),'/','0')
< replace(convert(varchar(7), getDate(), 111),'/', '0')

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

BammBamm
Starting Member

9 Posts

Posted - 2005-02-01 : 22:55:12

Basically, are the date values you're comparing equivalent to the format of the date coming back from GetDate()? Is the test_date column a "date" data type?

In any comparison, you've got to make the two comparison sets equivalent in data type and (possibly) format before comparing. If your original field is a date data type, the original query would be fine.

Presuming "2004/1", "2005/1" are actual values, and by extension "2004/12", "2005/2" etc. are also value options... Then none of the solutions presented thus far may fit.

Using spirit1's approach and modifying for a char/varchar data type instead of a date/time yields this:

where Convert(int,Replace(Right(t.test_date,2),'/','')) < Month(getDate()) and Convert(int,Left(t.test_date,4)) < year(getDate())

and it will work... if taking your question literally. If you want all dates less then a date to compare to, it will fail.

Example: t.test_date = 2004/6
compare date = 2005/1

t.test_date's "year" is less than the compare date, but the month (6) is greater.

hgorijal's approach is to homogenize the date pattern and compare. But if the "month" value after the "/" can be one or two characters, it too, will fail.

Try this:

Where
(Left(t.test_date,4) + right(('0'+ substring(t.test_date,patindex('/',t.test_date)+1,len(t.test_date)-(patindex('/',t.test_date)))),2))
<
convert(varchar(4),year(getdate())) + right('0' + convert(varchar,month(getdate()),2))

I could have missed some )'s or ('s here or there, but it should demonstrate the point and offer you something to work with.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-02-01 : 23:51:06

quote:
Originally posted by BammBamm
hgorijal's approach is to homogenize the date pattern and compare. But if the "month" value after the "/" can be one or two characters, it too, will fail.

Do you get a one charecter month using the syntax..convert(varchar(7), getdate(), 111) ... try it..

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

BammBamm
Starting Member

9 Posts

Posted - 2005-02-03 : 16:14:16

I stand corrected... hgorijal's approach works fine.

I'm not accustomed to using non-ISO, ODBC, or USA standards as a conversion reference, but it certainly does the trick!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources