Converting to DateTime

Question

Converting to DateTime

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
April 22, 2010 at 3:03 pm

#138704

I'm tasked with implementing Reporting from a new Database (Moodle) which is used for employee development quizes/exams.
But I've encountered a couple fields: "TimeStart" and "TimeEnd", and their values are for example: 1271782698; 1271782796 accordingly, and the table shows they're integer, but none of my convert syntax is working.
Does anybody know how I can get these values to show as Date or Date/Time, so that when they look at the report they know what the heck they mean?
thx,
JOhn

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm just guessing here but it seems like those are the seconds based on a start date (I don't know the startdate though, so just guessing...)

SELECT DATEADD(ss,1271782698,'19700101')

/*Result

2010-04-20 16:58:18.000

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

latingntlman

SSCarpal Tunnel

Points: 4348

April 22, 2010 at 3:25 pm

#1156474

You're a savior. That worked!!

thx.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Glad I could help 😀

One more thing to notice:

you shouldn't use that code within a WHERE clause.

Instead, convert the parameter you query against using something like

WHERE mycol > = DATEDIFF(ss,'19700101',@MinDate)

to make sure you can benefit from indexing (assuming it exists...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 4

latingntlman

SSCarpal Tunnel

Points: 4348

April 23, 2010 at 8:40 am

#1156906

Will definitely keep that in mind.

thx.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 5

Now that I have starttime/endtime as:

StartDate: 2010-04-20 16:58:18.000

EndDate: 2010-04-20 26:59:56.000

I need to calculate the difference as "TimeTaken" and show it in hh:mm:ss

What would be an effective wa to go here?

thx.

Lowell SSC Guru Points: 323518 More actions · Answer 6

now that you have the two vals as datetime, it should be easy;

here's some examples i've saved that pulls out the peices; you can concat them togeter for the format you want:

edited after testing and researching; found something that looks much better from a Michael Valentine Jones post:

select [Years ] = datediff(year,0,ET-ST),

[Months] = datepart(month,ET-ST),

[Days] = datepart(day,ET-ST),

[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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 7

latingntlman (4/23/2010)
Now that I have starttime/endtime as:
StartDate: 2010-04-20 16:58:18.000
EndDate: 2010-04-20 26:59:56.000
I need to calculate the difference as "TimeTaken" and show it in hh:mm:ss
What would be an effective wa to go here?
thx.

Assuming that the endDate value has a typo in it (hours are greater than 24), and the difference between the two is less than 24 hours, you can try something like this:

declare @start datetime;

declare @end datetime;

select

@start = '2010-04-20 16:58:18.000', @end = '2010-04-20 16:59:56.000'

select

convert(varchar(8), dateAdd(second, datediff(second, @start, @end), 0), 8) diff;

This will work fine even if the day part is different (process begins late at night one day and finishes early morning the next day. For example,

select

convert(

varchar(8), dateAdd(second, datediff(second,

'2010-04-20 23:58:18', '2010-04-21 03:24:16'), 0), 8) diff;

returns

diff

--------

03:25:58

Oleg

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

Lowell (4/23/2010)
now that you have the two vals as datetime, it should be easy;
here's some examples i've saved that pulls out the peices; you can concat them togeter for the format you want:
edited after testing and researching; found something that looks much better from a Michael Valentine Jones post:
select [Years ] = datediff(year,0,ET-ST),
[Months] = datepart(month,ET-ST),
[Days] = datepart(day,ET-ST),
[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,

The script you posted has off-by-one for both months and days. I think that this is because the datepart of the ET - ST always returns the datetime from the 0 date (1900-01-01), and so both month and day already have 1 for their respective parts, thus causing the off-by-one.

For example, your statement executed against a year ago and today:

select [Years ] = datediff(year,0,ET-ST),

[Months] = datepart(month,ET-ST),

[Days] = datepart(day,ET-ST),

[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,'2009/04/23'),

ET = convert(datetime,'2010/04/23')

) a

returns

Years Months Days Hours Minutes Seconds Milliseconds

----------- ----------- ----------- ----------- ----------- ----------- ------------

1 1 1 0 0 0 0

Oleg

Lowell SSC Guru Points: 323518 More actions · Answer 9

Oleg good catch! that's the diff between using datepart and calculating elapsed time; i'm fiddling with it now of to test for accuracy;

Thanks!

::edited; i think this seems to calculate elapsed time OK; any input?:

select [Years ] = datediff(year,0,ET-ST),

[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,'2009/04/23 12:00:00:000'),

ET = convert(datetime,'2010/05/25 13:01:01:003')

) a

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

Lowell,

Looks like all it will take is to add - 1 to [months] and [days] portion.

[Months] = datepart(month,ET-ST) -1,

[Days] = datepart(day,ET-ST) -1

Cool script though, will definitely land in the briefcase.

Thanks,

Oleg

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 11

You're right, the EndDate value was a typo...my bad. I'm implementing your second suggestion b/c it may carry to the next day, however, if the diff is 24 hours or more it returns a zero. How can I efficiently return diff higher than 24 hours. Example below:

select

convert(

varchar(8), dateAdd(second, datediff(second,

'2010-04-20 23:58:18', '2010-04-21 23:58:18'), 0), 8) diff;

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 12

latingntlman (4/23/2010)
You're right, the EndDate value was a typo...my bad. I'm implementing your second suggestion b/c it may carry to the next day, however, if the diff is 24 hours or more it returns a zero. How can I efficiently return diff higher than 24 hours. Example below:
select
convert(
varchar(8), dateAdd(second, datediff(second,
'2010-04-20 23:58:18', '2010-04-21 23:58:18'), 0), 8) diff;

If you need the difference of more than 24 hours than you can use Lowell's script, it is universal for any difference. The question now is how would you like the data to be formatted? With my snippet you get the values correctly for the range from 00:00:00 to 23:59:59. If the difference is more then what is the format you prefer? Suppose the difference is 5 days, 2 hours. Do you want the answer to become 122:00:00 (which no longer fits into your original hh:mm:ss request) or you would rather opt for something like 05 02:00:00.

Please let me know.

Oleg

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

I'd like to know why we're using SQL Server to format anything. This stuff should be done in a GUI if you have one and most reporting software qualifies.

--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)

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

Jeff Moden (4/24/2010)
I'd like to know why we're using SQL SDerver to format anything. This stuff should be done in a GUI if you have one and most reporting software qualifies.

Jeff,

You are absolutely correct, little or no formatting should be done in T-SQL. The availability of the GUI is the key here. If I need to run a statement and look at the results in the query results window then I can justify some formatting, such as

select convert(varchar(8), EndDateColumn - StartDateColumn, 8)

so I can get my results in desired hh:mm:ss format. This is because it might be faster to quickly view and analyze the data this way than firing up Visual Studio and spending few minutes to populate some grid with query results.

I the GUI is available then there is no doubt, all formatting related activities should be delegated to it.

Oleg