Converting int to Date
-
Hello,
I have two values like the following and would like to convert them to dates. I am sure that I am doing some mistakes in my code. Can someone please correct it.
-- 1177091222608 -> 5/11/2007
DECLARE @m bigint, @n int, @k int
SET @m =1177091222608
SET @n = CONVERT(int, @m/1000 + 1900)
SELECT @n = @m%1000
SELECT @n = @n%1000
SELECT @k = @n%1000
SELECT CAST(DATEADD(dd, @k, '01/01/1990') AS DateTime) AS 'value'
All the help will be highly appreciated.
ram
-
What does the value 1177091222608 represent? I can see some errors in the code but not sure how to fix it.
You are looking for a value of @k to be 6339. I get this by getting the datediff of 5/11/2007 and 1/1/1990. This would be the value you place in your query SELECT CAST(DATEADD(dd, @k, '01/01/1990') AS DateTime) AS 'value'. Instead, you are getting an @k value of 608, which is the mod value of 1177091222608/1000. Your code is taking the mod value multiple times and returning the same value.
Here's what I have done so far to troubleshoot if this helps
DECLARE
SET @m =1177091222608
SET @n = CONVERT(int, @m/1000 + 1900)
SELECT
@n
SELECT
@n = @m%1000
SELECT
@n
SELECT
@n = @n%1000
SELECT
@k = @n%1000
SELECT
SELECT
CAST(DATEADD(dd, @k, '01/01/1990') AS DateTime) AS 'value'
SELECT
DATEDIFF (dd, '01/01/1990', '5/11/2007') -- 6339
SELECT
CAST(cast('5/11/2007' as datetime) AS INT) --39211
-
Actually, I believe this is what they call a "UNIX DATETIME" and it represents the number of milliseconds since the midnight on 01/01/1970. That also makes your 5/11/2007 date incorrect (unless I'm missing something here)...
SELECT CAST('19700101' AS DATETIME)+(1177091222608/86400000.0)
--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) -
Hello All,
First of all, thanks for your help.
The value 1177091222608 is an integer datatype in a table that represents the original value of a date '5/11/2007'. For the purpose generating a report, I have to convert and send this to the report.
If this value represents the millonsofseconds, how do I convert it to a date format?
Thx,
Ram
-
Hm, I would say it's a question for primary school pupils.
How to get number of seconds if you know number of millions of seconds?
Then if you'll use DATEADD function you'll figure out that number does not represent millions of seconds.
_____________
Code for TallyGenerator -
Show us the INT value for '5/10/2007' and we'll be able to tell you (but I think you're wrong about the date)
--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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply