August 18, 2009 at 2:33 am
Hello All
One of the column in my database contains createdate which gets stored in integer format just like that 1231151827 i want to get it converted into datetime format
August 18, 2009 at 2:50 am
Hi,
Are you sure this int column having 10 digit characters?
August 18, 2009 at 2:55 am
At a guess , that is the number of seconds from 01jan1970
try this
select (1231151827/60)
select 20519197/60
select 341986/24
select dateadd(dd,14249,'19700101')
August 18, 2009 at 2:59 am
Hi,
try this
select convert(datetime,40010/*your int value*/,112/*your format*/)
August 18, 2009 at 3:04 am
DATETIME to INT
declare @time1 datetime
set @time1 = getdate()
select convert(int,@time1)
print @time1
INT to DATETIME
declare @time int
set @time = 40042
select convert(datetime,@time,112)
print @time
August 18, 2009 at 4:49 am
Thanks a lot it worked
thanks to all who supported
it got resolved by
putting the query
select (1231151827/60)
select 20519197/60
select 341986/24
select dateadd(dd,14249,'19700101')
August 18, 2009 at 4:50 am
harishchede (8/18/2009)
Thanks a lot it workedthanks to all who supported
it got resolved by
putting the query
select (1231151827/60)
select 20519197/60
select 341986/24
select dateadd(dd,14249,'19700101')
August 18, 2009 at 6:11 am
Dave Ballentine showed you step by step how the conversion is going to work; but if you use the final answer, you don't get the timne portion, just the correct date;
you lose the modulus of the integer division;
you should shortcut and just use the DATEADD function to add the total seconds:
select dateadd(dd,14249,'19700101') --add the offset for the stored date
--2009-01-05 00:00:00.000
select (1231151827 % 60) --sec to min gain 7 seconds from
select 20519197 % 60 --min to hour gain 37 minutes
select 341986 % 24 --hour to days gain 10 hours
select dateadd(second,1231151827,'19700101')
--2009-01-05 10:37:07.000
Lowell
August 19, 2009 at 9:14 am
That is a pretty interesting problem. I actually discovered that I have a similar problem in a db I just acquired so these solutions will come in handy for me as welL!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
August 19, 2009 at 9:25 am
After Dave inferred that it was the # seconds since 01/01/1970, it was pretty easy...but how the heck did he figure out the starting date?
Dave had you tripped over the same issue before? is it a common conversion from some other system(ie Oracle or DB2 or something?)
Lowell
August 19, 2009 at 9:28 am
Lowell (8/19/2009)
After Dave inferred that it was the # seconds since 01/01/1970, it was pretty easy...but how the heck did he figure out the starting date?Dave had you tripped over the same issue before? is it a common conversion from some other system(ie Oracle or DB2 or something?)
IIRC, this is how *nix stores date/time values.
August 19, 2009 at 9:36 am
Lynn Pettis (8/19/2009)
IIRC, this is how *nix stores date/time values.
Yup , no magic mystical powers involved
May 28, 2013 at 1:07 am
I'm assuming the Unix server is always storing this in the time zone UTC, so if I want it in CST it is always -6 hours?
April 19, 2018 at 2:37 am
Lowell - Tuesday, August 18, 2009 6:11 AMDave Ballentine showed you step by step how the conversion is going to work; but if you use the final answer, you don't get the timne portion, just the correct date;you lose the modulus of the integer division;you should shortcut and just use the DATEADD function to add the total seconds:select dateadd(dd,14249,'19700101') --add the offset for the stored date--2009-01-05 00:00:00.000select (1231151827 % 60) --sec to min gain 7 seconds fromselect 20519197 % 60 --min to hour gain 37 minutesselect 341986 % 24 --hour to days gain 10 hoursselect dateadd(second,1231151827,'19700101')--2009-01-05 10:37:07.000
very good
work done but i have other problem
i wanna separate the output into tow column like this
date time
19-4-2018 08:59:17
i used ur statement ,dateadd(second,nDateTime,'19700101') as Ndate
the output of this
--------------------------------------
2018-04-19 08:59:17.000
please help me
thank u
April 19, 2018 at 6:35 am
hamed.alzubide - Thursday, April 19, 2018 2:37 AMLowell - Tuesday, August 18, 2009 6:11 AMDave Ballentine showed you step by step how the conversion is going to work; but if you use the final answer, you don't get the timne portion, just the correct date;you lose the modulus of the integer division;you should shortcut and just use the DATEADD function to add the total seconds:select dateadd(dd,14249,'19700101') --add the offset for the stored date--2009-01-05 00:00:00.000select (1231151827 % 60) --sec to min gain 7 seconds fromselect 20519197 % 60 --min to hour gain 37 minutesselect 341986 % 24 --hour to days gain 10 hoursselect dateadd(second,1231151827,'19700101')--2009-01-05 10:37:07.000
very good
work done but i have other problem
i wanna separate the output into tow column like thisdate time
19-4-2018 08:59:17i used ur statement ,dateadd(second,nDateTime,'19700101') as Ndate
the output of this
--------------------------------------
2018-04-19 08:59:17.000please help me
thank u
You are hijacking a thread when you should have opened yours. However, I will grant you recognition from possibly searching an answer before posting.
Here's an article that shows some options that you have , along with a performance comparison among them.
http://www.sqlservercentral.com/blogs/dwainsql/2015/08/28/the-fastest-way-to-combine-date-and-time-data-types-to-a-datetime/
The best solution also depends on the data types that you're using as input and output.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy