December 16, 2011 at 1:19 pm
Hello,
Is there a way using TSQL to convert an integer to year, month and days
for e.g. 365 converts to 1year 0 months and 0 days
366 converts to 1year 0 months and 1 day
20 converts to 0 year 0 months and 20 days
200 converts to 0 year 13 months and 9 days
408 converts to 1 year 3 months and 7 days .. etc
many thanks,
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
December 16, 2011 at 1:24 pm
Can you provide how you are planning to use this?
There may be an alternative.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 16, 2011 at 1:29 pm
the DATEADD function can do this...adding those 366 days to a desired starting date...by default SQL is 1900-01-01, soe something liek this will return the following:
--returns 1901-01-02 00:00:00.000
SELECT DATEADD(dd,366,0)--same as DATEADD(dd,365,'1900-01-01 00:00:00.000')
if you need to add to a specific date, swap out the zero for your desired starting date.
Lowell
December 16, 2011 at 1:53 pm
if you wanted something like elapsed time, you really have to do it between two dates, and not from an integer...the elapsed number of months depends on whether the month has 28 to 31 days in it; same for a year...some years have 365, leap years have 366 days.
select [Years ] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/23 04:05:45.443')
) a
Lowell
December 16, 2011 at 2:18 pm
Thanks Lowel !
But my problem is that the data is integer and I get it as 465, 356, etc .. I need to convert it into meaning full data.
I guess I am off the track, but someone might be able to help out.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
December 16, 2011 at 2:18 pm
Thanks Lowel !
But my problem is that the data is integer and I get it as 465, 356, etc .. I need to convert it into meaning full data.
I guess I am off the track, but someone might be able to help out.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
December 16, 2011 at 2:31 pm
SQLQuest29 (12/16/2011)
Thanks Lowel !But my problem is that the data is integer and I get it as 465, 356, etc .. I need to convert it into meaning full data.
I guess I am off the track, but someone might be able to help out.
You may have to adjust or recognize leap years, for example:
2012 is a leap year as was 2008
A Leap year is a year divisible by 4 with no remainder and NOT divisible by 100 with no remainder.
SELECT DATEADD(dd,365,'2011-01-01 00:00:00.000')
,DATEADD(dd,730,'2011-01-01 00:00:00.000')
Results:
2012-01-01 00:00:00.0002012-12-31 00:00:00.000
December 16, 2011 at 2:34 pm
if the integer was something like 40891, we could infer that it was 40891 days since the beginning of SQL time, which would, by coincidence, be today (12/16/2011.
without a frame of reference an integer doesn't have any relationship to a date, and an accurate conversion of elapsed time cannot be calculated.
if you just want to ballpark and say all monts are 30 days, then just do integer division
/*
YEARSMONTHSDAYS
1311
*/
declare @days int
SET @days = 466
SELECT
[YEARS] = @days / 365,
[MONTHS] = (@days % 365) / 30,
[DAYS] = (@days % 365) % 30
Lowell
December 16, 2011 at 2:36 pm
SQLQuest29 (12/16/2011)
But my problem is that the data is integer and I get it as 465, 356, etc .. I need to convert it into meaning full data.
You're going to need SOME reference date in order to convert this into something meaningful, because, as has already been stated, years can have 365 or 366 days, and months can have 28, 29, 30, or 31 days. The reference date can be absolute (e.g., the way that SQL uses 1900-01-01) or relative (based on another field in the table).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 16, 2011 at 3:02 pm
Lowell (12/16/2011)
if the integer was something like 40891, we could infer that it was 40891 days since the beginning of SQL time, which would, by coincidence, be today (12/16/2011.without a frame of reference an integer doesn't have any relationship to a date, and an accurate conversion of elapsed time cannot be calculated.
if you just want to ballpark and say all monts are 30 days, then just do integer division
/*
YEARSMONTHSDAYS
1311
*/
declare @days int
SET @days = 466
SELECT
[YEARS] = @days / 365,
[MONTHS] = (@days % 365) / 30,
[DAYS] = (@days % 365) % 30
This will work for me.. as I need to ballpark the figure ...
Many thanks !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
December 16, 2011 at 4:33 pm
Why settle for an approximate date. Create a table calendar and use the value of interval to return a exact date.
CREATE TABLE #Calendar
(xdate SMALLDATETIME NOT NULL, Interval INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
GO
SET NOCOUNT ON
DECLARE @d SMALLDATETIME
SET @d = '20050101' -- Calendar will stat on 2005-01-01 00:00:00
WHILE @d < '20150101' -- Calendar will end on 2014-12-31 00:00:00
BEGIN
INSERT #Calendar(xdate) SELECT @d
SET @d = @d + 1
END
SELECT xdate FROM #Calendar WHERE Interval = 536
Returns:2006-06-20 00:00:00
SELECT MIN(xdate),MAX(xdate) FROM #Calendar
Returns:
2005-01-01 00:00:002014-12-31 00:00:00
DROP TABLE #Calendar
Now you can add additional items, if they could be of value to you. For example:
CREATE TABLE #Calendar
(xdate SMALLDATETIME NOT NULL, MonthName VARCHAR(10),DayName VARCHAR(10),Interval INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
GO
SET NOCOUNT ON
DECLARE @d SMALLDATETIME
SET @d = '20050101' -- Calendar will stat on 2005-01-01 00:00:00
WHILE @d < '20150101' -- Calendar will end on 2014-12-31 00:00:00
BEGIN
INSERT #Calendar(xdate,MonthName,DayName) SELECT @d, DATENAME(mm,@d),DATENAME(dw,@d)
SET @d = @d + 1
END
SELECT xdate,MonthName,DayName FROM #Calendar WHERE Interval = 536
Returns:
xdate MonthNameDayName
2006-06-20 00:00:00 JuneTuesday
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply