Select rows where int formatted date < getDate()

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)

Select rows where int formatted date < getDate()

Author

Topic

mike.bull
Starting Member

9 Posts

Posted - 2008-05-26 : 09:54:11

Hey there,

I have a field called renew_date which is of (int) type, and represents a date in the format 'yyyymmdd'.

Eg. If the renew date were yesterday the field would contain: 20080525.

I need a select statment that will determine if the date contained in this field is '<' todays date.

I have wrote a select statement that works, however I'm wondering if there is a better way to handle this.


select * from sometable 
where renew_date < 
convert(int, convert(varchar, DATEPART(yyyy, getDate())) + 
convert(varchar, Right('0' + Convert(VarChar(2), DATEPART(mm, getDate())),2)) +
convert(varchar, Right('0' + Convert(varchar(2), DATEPART(dd, getDate())),2)))

Any help you may provide is greatly appreciated.

Thanks
--Mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 10:02:52

where int_datecol<convert(varchar(8),getdate(),112)+0

Also always use proper DATETIME datatype to store dates and let front end application do the formation

Madhivanan

Failing to plan is Planning to fail

mike.bull
Starting Member

9 Posts

Posted - 2008-05-26 : 10:38:32

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources