yyyymmdd date calculation - 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 2005 Forums

Transact-SQL (2005)

yyyymmdd date calculation

Author

Topic

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-12-08 : 14:18:58

My dates are stored like yyyymmdd. I need to find the previous day for a query. My problem is, if I take the date of 20091201 and subtract 1 day I get 20091200. How do I get it to be 20091130?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 14:26:53

What is the datatype of that field?

If 'int' then use..

select convert(varchar(8),dateadd(day,-1,convert(varchar(8),<urfield>)),112)

If varchar/datetime, use...

select convert(varchar(8),dateadd(day,-1,<urfield>),112)

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2009-12-08 : 14:42:12

Thanks vijay that's exactly what I was looking for.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 14:56:40

You're welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-10 : 01:53:34

Beware of the bad dates
You need to use len and isdate functions too
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources