| Author |
 Topic  |
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
 Posted - 01/03/2008 : 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 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2869 Posts |
Posted - 01/03/2008 : 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
Philippines
159 Posts |
Posted - 01/03/2008 : 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 - 01/03/2008 : 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
Flowing Fount of Yak Knowledge
USA
2869 Posts |
Posted - 01/03/2008 : 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
Philippines
159 Posts |
Posted - 01/03/2008 : 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 - 01/03/2008 : 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
|
Edited by - X002548 on 01/03/2008 13:29:22 |
|
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
Posted - 01/03/2008 : 13:35:47
|
how about if the date always starts in 01?
thanks!
Funnyfrog |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
Posted - 01/03/2008 : 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 |
|
|
shemayb
Posting Yak Master
Philippines
159 Posts |
Posted - 01/03/2008 : 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
USA
391 Posts |
Posted - 01/03/2008 : 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
Flowing Fount of Yak Knowledge
4137 Posts |
Posted - 01/03/2008 : 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
India
22624 Posts |
Posted - 01/04/2008 : 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 |
|
|
| |
Topic |
|
date add
