September 1, 2008 at 6:25 am
Hi All,
I have written a code recently to get the first date of next year.
say for example,
Create table t1
(
Eno int,
Date datetime
)
Insert into t1
select 1,'Jun/30/2006'
union all
select 2,'May/31/2004'
union all
select 3,'Jan/01/2007'
union all
select 4,'Aug/10/2005'
Expected Output:
Eno InceptionDate
1 01/01/2007
2 01/01/2005
3 01/01/2007 - No change
4 01/01/2006
I have written the below query.
Select Eno,InceptionDate = case when Datepart(dd,Date) = 1 And Datepart(mm,Date) = 1 Then Date
else '01/'01/' + convert(varchar(4),datepart(yy,Date)+1)
end
from t1
Alternate ways are welcome !
karthik
September 1, 2008 at 7:35 am
Count number of whole years between the date and "zero date" (1/01/1900) and add this number +1 to "zero date".
It's gonna be beginning of next year.
Use datetime functions for it.
Same method may be used for month, weeks etc.
_____________
Code for TallyGenerator
September 1, 2008 at 9:26 am
Sergiy, that will not work for January 1st dates.
However, you are not that far from a working suggestion.
Try this
DECLARE@Sample TABLE
(
Eno int,
Date datetime
)
Insert@Sample
select 1,'2006-06-30' union all
select 2,'2004-05-31' union all
select 3,'2007-01-01' union all
select 4,'2005-08-10'
SELECTEno,
Date AS [Original date],
DATEADD(YEAR, DATEDIFF(YEAR, '19000101', Date) + 1, '19000101') AS Sergiy,
DATEADD(YEAR, DATEDIFF(YEAR, '19000101', Date - 1), '19010101') AS Peso
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
September 1, 2008 at 9:40 am
HEre is another solution
DECLARE @Sample TABLE
(
Eno int,
Date datetime
)
Insert @Sample
select 1,'2006-06-30' union all
select 2,'2004-05-31' union all
select 3,'2007-01-01' union all
select 4,'2005-08-10'
SELECT
Eno,
Date AS [Original date],
DATEADD(YEAR,DATEDIFF(YEAR,0,Date-1)+1,0)
FROM @Sample
I simply use 0 as the base date
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 1, 2008 at 7:05 pm
Peso (9/1/2008)
Sergiy, that will not work for January 1st dates.However, you are not that far from a working suggestion.
Thanks, Peso, it's good to know there is some tester always around to implement and test my ideas. 😉
I'd doubt your consideration about "working suggestion" though.
As any number with limited precision a datetime value indicates infinite number of time moments between the value itself and next precision step.
So, value '20070101' indicates any time moment >= '2007-01-01 00:00:00.000' and < '2007-01-01 00:00:00.003'
If the system records only dates, not times, then its precision equals 1 day and '20070101' indicates any time >= '2007-01-01 00:00:00.000' and < '2007-01-02 00:00:00.000'
Because there is infinite number (it's infinitely more than any very big number) of moments withing any of those periods then probability of equality (= '2007-01-01 00:00:00.000' ) is zero. So, it may be safely taken as > '2007-01-01 00:00:00.000'
If your current time is '2007-01-01' then you've already entered the year 2007 and the beginning of your next year is '2008-01-01'.
If to look at the resultset returned by your test code it becomes clear that my result is right and your one is wrong.
_____________
Code for TallyGenerator
September 1, 2008 at 11:59 pm
Not according to OP requirements.
He is only storing dates with "no time information", ie time set to 00:00:00
And he wants dates for January 1st of every year to remain to current year, and all other dates be forwarded to January 1st of following year.
Didn't you read the spec?
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply