Datefunction - 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

General SQL Server Forums

New to SQL Server Programming

Datefunction

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-06-14 : 04:53:07

Dear All,

I need to retrieve last year data based on policystartdate + 23 days

ie.lastyear current date + 23 days = 7 July 2011(policystartdate).i need all the record which has policystartdate (july 07.2011)

I need to automat this script. I tried few date function but not helpful.

Any help will be highly appreciated!

Thanks

Regards,
SG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-14 : 04:57:48

dateadd(year,-1,dateadd(day,23,dateadd(day,datediff(day,0,getdate()),0)))

No, you're never too old to Yak'n'Roll if you're too young to die.

satheesh
Posting Yak Master

152 Posts

Posted - 2012-06-14 : 06:12:03

Thank You webfred!
Working Perfect!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-14 : 06:53:54

Net really. If the "-1 year and +23 days" spans over a leapday (29 feb), there will be one day missing in the formula above. Change order and withdraw 1 year first, and then add 23 days, to get correct result.

DECLARE	@Sample TABLE
	(
		dt DATETIME NOT NULL
	)

INSERT	@Sample
VALUES	('20130215 09:10:03'),
	('20120215 22:15:21')

-- Solutions
SELECT	dt,
	dateadd(year,-1,dateadd(day,23,dateadd(day,datediff(day,0,dt),0))) AS WebFred,
	DATEADD(DAY, 23 + DATEDIFF(DAY, '19000101', DATEADD(YEAR, -1, dt)), '19000101') AS SwePeso
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-14 : 07:03:37

quote:
Originally posted by SwePeso

Net really. If the "-1 year and +23 days" spans over a leapday (29 feb), there will be one day missing in the formula above. Change order and withdraw 1 year first, and then add 23 days, to get correct result.
DECLARE @Sample TABLE
(
dt DATETIME NOT NULL
)

INSERT @Sample
VALUES ('20130215 09:10:03'),
('20120215 22:15:21')

-- Solutions
SELECT dt,
dateadd(year,-1,dateadd(day,23,dateadd(day,datediff(day,0,dt),0))) AS WebFred,
DATEADD(DAY, 23 + DATEDIFF(DAY, '19000101', DATEADD(YEAR, -1, dt)), '19000101') AS SwePeso
FROM @Sample

N 56°04'39.26"
E 12°55'05.63"

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-14 : 07:11:55

Fred, i think the "Like a boss" pic is more Peso.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-14 : 07:20:42

quote:
Originally posted by DonAtWork

Fred, i think the "Like a boss" pic is more Peso.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

But that's me smiling afflicted...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources