SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date add
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  12:41:10  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
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 - 01/03/2008 :  12:59:40  Show Profile  Reply with Quote
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



Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2008 :  13:00:39  Show Profile  Reply with Quote
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
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  13:07:45  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/03/2008 :  13:17:06  Show Profile  Reply with Quote
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



Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2008 :  13:22:11  Show Profile  Reply with Quote
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)
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  13:23:28  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/03/2008 :  13:27:57  Show Profile  Reply with Quote
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




Edited by - X002548 on 01/03/2008 13:29:22
Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  13:35:47  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
how about if the date always starts in 01?

thanks!

Funnyfrog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/03/2008 :  13:37:10  Show Profile  Reply with Quote
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



Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  13:45:04  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/03/2008 :  13:47:42  Show Profile  Reply with Quote
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



Go to Top of Page

shemayb
Posting Yak Master

Philippines
159 Posts

Posted - 01/03/2008 :  13:49:03  Show Profile  Send shemayb a Yahoo! Message  Reply with Quote
yeah!i'm playinh with it right now...thank you for giving me samples..i really do appreciate it..thanks!

Funnyfrog
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 01/03/2008 :  13:51:08  Show Profile  Reply with Quote
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



Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/03/2008 :  14:24:57  Show Profile  Reply with Quote
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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/04/2008 :  07:54:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000