date add - 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

date add

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 12:41:10

Hi!

I just wanted to ask what is the correct script for this situation:
i entered the FROM date which is 10/01/2000 and TO date which is 09/30/2001. The result of the query should be 2 years before which is 01/01/1998 for the FROM date and 12/31/1998 for the TO date. but the result of my query is this 10/01/1998 for the FROM date and 11/30/1998 for the TO date.These are my statements:This is for getting the from date - CONVERT(CHAR(10), DATEADD(YEAR, -2, @sDate), 101) AS ydate,this is for getting the TO date - convert(char(10),dateadd(mm,-(month(dateadd(yy,-1,@sDate))),dateadd(dd,-(day(dateadd(yy,-1,@sDate))),dateadd(yy,-1,@sDate))),101) as ndate.Thank you!

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 12:59:40

I think I just went blind

What do you really want?

Can you post sample data and what the expected results should be at least?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-01-03 : 13:00:39

declare @sdate datetime

set @sdate = '10/10/2000'

select [FromDate] = DATEADD(year,DATEDIFF(year,'01/01/1902',@sdate) ,0)

,[ToDate] = DATEADD(year,DATEDIFF(year,'12/31/1901',@sdate) ,-1)

Jim

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 13:07:45

i just wanted to display which will always start at january 01 at whatever year.

for example my FROM date is 10/01/2000, i wanted to get the date two years before my FROM date but in my script:CONVERT(CHAR(10), DATEADD(YEAR, -2, @sDate), 101) AS ydate my result is 10/01/1998, the result that i want is a 01/01/1998..the month to be displayed is the first month of the year same as the date to be displayed should be the first day of the month.

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:17:06

This


 DECLARE @d datetime
     SET @d = '10/01/2000'
  SELECT  CONVERT(varchar(10),@d,101) AS OriginalDate
	, CONVERT(varchar(10),DATEADD(yy,-2,@d),101) AS TwoYearsEarlier

Gives you this


OriginalDate TwoYearsEarlier 
------------ --------------- 
10/01/2000   10/01/1998

(1 row(s) affected)

Is that what you want?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-01-03 : 13:22:11

declare @sdate datetime

set @sdate = '10/10/2000'

select [FromDate] = CONVERT(varchar(10),DATEADD(year,DATEDIFF(year,'01/01/1902',@sdate) ,0),101)

,[ToDate] = CONVERT(varchar(10),DATEADD(year,DATEDIFF(year,'12/31/1901',@sdate) ,-1),101)

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 13:23:28

hello Brett,

yeah,it is two years earlier.

OriginalDate TwoYearsEarlier
------------ ---------------
10/01/2000 10/01/1998

but the month should always start in january,how can i get these ff results?

OriginalDate TwoYearsEarlier
------------ ---------------
10/01/2000 01/01/1998

thanks!

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:27:57

Well that's kinda arbitrary, so the code will be to



 DECLARE @d datetime
     SET @d = '10/01/2000'
  SELECT  CONVERT(varchar(10),@d,101) AS OriginalDate
	, '01'+SUBSTRING(CONVERT(varchar(10),DATEADD(yy,-2,@d),101),3,8) AS TwoYearsEarlier

Gives you


OriginalDate TwoYearsEarlier 
------------ --------------- 
10/01/2000   01/01/1998

(1 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 13:35:47

how about if the date always starts in 01?

thanks!

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:37:10

See how I'm giving sample code a data....I don't know what you mean

Show me what you mean

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 13:45:04

if have date which is 10/11/2000 and the year should be 2 years earlier the result whould be:

OriginalDate TwoYearsEarlier
------------ ---------------
10/11/2000 01/11/1998

how about if the result is like this:

OriginalDate TwoYearsEarlier
------------ ---------------
10/11/2000 01/01/1998

even if the date given is 01/11/2000 the result TwoYearsEarlier would always start to 01/01.

thank you so much!

Funnyfrog

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 13:47:42

OK, now you're just pulling my leg....I'm sure you can figure out if you just play with sample code I gave

Cut and paste it into QA or SSMS or whatever you are using...if you are using anything

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

shemayb
Posting Yak Master

159 Posts

Posted - 2008-01-03 : 13:49:03

yeah!i'm playinh with it right now...thank you for giving me samples..i really do appreciate it..thanks!

Funnyfrog

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-01-03 : 13:51:08

quote:
Originally posted by shemayb

if have date which is 10/11/2000 and the year should be 2 years earlier the result whould be:

OriginalDate TwoYearsEarlier
------------ ---------------
10/11/2000 01/11/1998

how about if the result is like this:

OriginalDate TwoYearsEarlier
------------ ---------------
10/11/2000 01/01/1998

even if the date given is 01/11/2000 the result TwoYearsEarlier would always start to 01/01.

thank you so much!

Funnyfrog

Come on now. Surely you can think this through a little on your own, and not expect us to do everything?

At least put a little effort into learning things and figuring out things yourself. Brett gave you more than enough information to figure out how to do what you want:




 DECLARE @d datetime
     SET @d = '10/01/2000'
  SELECT  CONVERT(varchar(10),@d,101) AS OriginalDate
	, '01/01'+SUBSTRING(CONVERT(varchar(10),DATEADD(yy,-2,@d),101),7,4) AS TwoYearsEarlier

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-03 : 14:24:57

Assuming your date is an actual datetime you could do date math on it:

DECLARE @Yak DATETIME
SET @Yak = '20001011'

SELECT 
	@Yak,
	DATEADD(YEAR, YEAR(@Yak) - 1902, 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 07:54:23

1 Always use proper DATETIME datatype to store dates
2 Method handling on dates

Declare @d datetime
set @d='20050904'
select @d as OriginalDate, dateadd(year,datediff(year,0,@d)-2,0) TwoYearsEarlier

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources