Converting YYYYDDD Format To DATE From Type INT
-
Hello,
I am working with an INT column called [Last Sale Date]. A sample of the data is as follows;
2006314
2006018
The first four digits represent the year, and the last three represent the number of days since the beginning of that year. I have put together a query that gives me the actual date based on the begining of said year + the number of days. The query is as follows;
SELECT
IntYear
AS StartingPoint,
IntDay AS DaysFromStartingPoint,
DATEADD(DAY,(IntDay-1),IntYear) AS DateResult
FROM
(
SELECT
CASE
WHEN NOT [Last Sale Date] + 1 = 1 THEN
CAST('01'+'-'+'01'+'-'+SUBSTRING(CAST([Last Sale Date] AS Varchar(8)),1,4)AS SMALLDATETIME)
END AS IntYear,
CASE
WHEN NOT ISNULL([Last Sale Date],0) = 0 THEN
CAST(SUBSTRING(CAST([Last Sale Date] AS Varchar(8)),5,3)AS INT)
END AS IntDay
FROM
dbo.tblPaymHistCurrentWeek
)
AS A
This works okay, but If I try to add an ELSE condition in the first CAST statement to just show the value of [Last Sale Date], I get the first day of 1900 where the [Last Sale Date] values should show up as zero. For some reason, the CAST operation that occurs when the first CASE is true seems to cast the data no matter how the condition turns out.
How can I correctly add an ELSE statement to the first CASE to get the correct values for [Last Sale Date] when the condition is False?
Thank you for your help!
CSDunn
-
What is the correct value if the condition is false? If I read the code right, the else condition is that Last Sale Date = 0. So what is the meaning of Last Sale Date = 0?
Run the following in query analyzer: Select DATEADD(DAY,(null-1),0)
That is what your query would return if you change the query the way you want.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Create UDF and use it in your scripts.
@IntDate will be a parameter for this UDF.
SELECT @Year = @IntDate / 1000
SELECT @Day = IntDate - @Year*1000
SELECT @Date = dateadd(yy, @Year, dateadd(dd, @Day, 0))
_____________
Code for TallyGenerator -
Thank you for your help!
CSDunn
-
-1900 Serqiy
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CSDunn,
You can either use the forumla in the following in a UDF or directly...
SELECT DATEADD(yy,[Last Sale Date]/1000-1900,[Last Sale Date]-([Last Sale Date]/1000*1000)-1) AS [Last Sale Date] FROM dbo.tblPaymHistCurrentWeek
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks again!
CSDunn
-
And one more way to do it:
select dateadd(year,[Last Sale Date]/1000-1900,[Last Sale Date]%1000-1) from (select [Last Sale Date] = 2006014 union all select [Last Sale Date] = 2006018 ) a
-
Cool... forgot about the mod. Thanks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply