count how many days in years or months

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

Old Forums

CLOSED - General SQL Server

count how many days in years or months

Author

Topic

khautinh
Starting Member

10 Posts

Posted - 2005-07-12 : 14:28:18

I have 010101 which means 1year, 1 month and 1 day. I want to know how manay days for 010101. I Just want to know in a generic way.

Are there any ways to do in SQL server 2000?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 14:32:16

The answer will differ depending on what year the 010101 represents. leap years will have an extra day. Not to mention days for which month. (could be 28, 29, 30 or 31)

Be One with the Optimizer
TG

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-12 : 19:18:55

[code]
DECLARE
@StartDate1 DATETIME,
@StartDate2 DATETIME,
@StartDate3 DATETIME,
@StartDate4 DATETIME,
@StartDate5 DATETIME,
@StartDate6 DATETIME,
@StartDate7 DATETIME,
@StartDate8 DATETIME,
@StupidString CHAR(6),
@Years INT,
@Months INT,
@Days INT

SELECT
@StartDate1 = '01/01/97',
@StartDate2 = '01/01/98',
@StartDate3 = '01/01/99',
@StartDate4 = '01/01/00',
@StartDate5 = '01/01/01',
@StartDate6 = '01/01/02',
@StartDate7 = '01/01/03',
@StartDate8 = '01/01/04',
@StupidString = '010902'

SELECT
@Years = LEFT(@StupidString,2),
@Months = LEFT(RIGHT(@StupidString,4),2),
@Days = RIGHT(@StupidString,2)

SELECT @Years, @Months, @Days

SELECT
@StartDate1 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate1))) AS EndDate,
DATEDIFF(DD,@StartDate1,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate1)))) AS DayDifference

SELECT
@StartDate2 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate2))) AS EndDate,
DATEDIFF(DD,@StartDate2,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate2)))) AS DayDifference

SELECT
@StartDate3 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate3))) AS EndDate,
DATEDIFF(DD,@StartDate3,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate3)))) AS DayDifference

SELECT
@StartDate4 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate4))) AS EndDate,
DATEDIFF(DD,@StartDate4,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate4)))) AS DayDifference

SELECT
@StartDate5 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate5))) AS EndDate,
DATEDIFF(DD,@StartDate5,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate5)))) AS DayDifference

SELECT
@StartDate6 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate6))) AS EndDate,
DATEDIFF(DD,@StartDate6,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate6)))) AS DayDifference

SELECT
@StartDate7 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate7))) AS EndDate,
DATEDIFF(DD,@StartDate7,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate7)))) AS DayDifference

SELECT
@StartDate8 AS StartDate,
DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate8))) AS EndDate,
DATEDIFF(DD,@StartDate8,DATEADD(DD,@Days,DATEADD(MM,@Months,DATEADD(YY,@Years,@StartDate8)))) AS DayDifference
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources