CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008datetime
İbrahim Akgün
İbrahim Akgün

Reputation: 1577

How to convert day to Year-Month-Day format with T-SQL in SQL Server 2008?

I have an int field (Days) in myTable. How can I format it to Year-Month-Day String?

I want to do this with T-SQL SQL Server 2008.

For example:

65 Days = 0 Year 2 Month 5 Day

Upvotes: 1

Views: 6569

Answers (3)

KPOK
KPOK

Reputation: 1

I got to this code...

select
cast((DATEDIFF(year, StartDate, getdate()))as CHAR(3)) +'years '+
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0 
then
cast(DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()) -1 AS CHAR(2))
else 
cast (DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())AS CHAR(2))
end +'months '+
-------------------------------------------
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0
then
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())-1,
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()) as CHar(2))
else
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE())as Char(2))
end+'days' as
Lenghtofservice
--DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)) 
 from dbo.Employee

Upvotes: -2

gbn
gbn

Reputation: 432672

The "zero" here is 01 Jan 1900. This gives a date related to this

SELECT
   dateadd(day, myColumn, 0)
FROM
   myTable

If you want to break out the component values separately

WITH CTE AS
(
  SELECT
     dateadd(day, myColumn, 0) AS theDate 
  FROM
     myTable
)
SELECT
  DATEPART(year, theDate) - 1900,
  DATEPART(month, theDate),
  DATEPART(day, theDate)
FROM
  CTE

Then you also have the leap year issues. After 28 Feb 1904, you have 29 Feb 1904 There isn't a 100% guaranteed way to express "days" as years + months + days because month lengths very every 4 years or so.

In case I've misunderstood, to rebase it to year "zero"

SELECT
   DATEADD(day, DATEDIFF(day, '0001-01-01', 0), CAST(DATEADD(day, myColumn, 1) as date))
FROM
   myTable

Upvotes: 3

Ralf de Kleine
Ralf de Kleine

Reputation: 11745

declare @days int
set @days = 65
select 
cast(year(dateadd(day,@days,0))-1900 as varchar) + ' Year '
+ cast(month(dateadd(day,@days,0)) as varchar) + ' Month '
+ cast(day(dateadd(day,@days,0)) as varchar) + ' Day'

Keep in mind that this is measured from 1st of junuari and other problems

Upvotes: 0

Related Questions