hours,minutes Between Date Format Of Type Yyyymmddhhmmss

Question

hours,minutes Between Date Format Of Type Yyyymmddhhmmss

rj529

Ten Centuries

Points: 1194
More actions
December 7, 2012 at 12:23 pm

#393769

I have 2 dates format like 200903021124 and 200903030254.how to get hours,minutes Between those dates?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

have a look at the datediff function

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

ScottPletcher SSC Guru Points: 101130 More actions · Answer 2

--If the final total will be <= 24 hrs, you can do this:

SELECT CONVERT(char(5), DATEADD(MINUTE, DATEDIFF(MINUTE, STUFF(STUFF(date_column1, 9, 0, ' '), 12, 0, ':'), STUFF(STUFF(date_column2, 9, 0, ' '), 12, 0, ':')), 0), 8)

FROM (

SELECT '200903021124' AS date_column1, '200903030254' AS date_column2

) AS test_data

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 3

you will hear this about a million times, but the number one rule is to store datetimes as datatimes; not as strings or bigints or any other datatype;

when you don't follow that simple rule, you lose the ability to compare dates, add them up, and do so much more without jumping through hoops to turn the strings back into date times again.

I know sometimes it's not your fault and you inherit bad data structures, but you need to make the effort to convert teh data to the proper data types.

here's one example, assuming your data is strings.

With mySampleData

AS

(

SELECT '200903021124' AsAlmostButNotQuiteADate UNION ALL

SELECT '200903030254'

)

SELECT CONVERT(datetime, STUFF(STUFF(AsAlmostButNotQuiteADate,11,0,':'),9,0,' ') + ':00.000')

FROM mySampleData

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!

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

rajkiran.panchagiri (12/7/2012)
I have 2 dates format like 200903021124 and 200903030254.how to get hours,minutes Between those dates?

Are those stored as BIGINT or some character based datatype? Can't tell from here. And, yes, it does make a difference.

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Lowell (12/7/2012)
you will hear this about a million times, but the number one rule is to store datetimes as datatimes; not as strings or bigints or any other datatype;
when you don't follow that simple rule, you lose the ability to compare dates, add them up, and do so much more without jumping through hoops to turn the strings back into date times again.
I know sometimes it's not your fault and you inherit bad data structures, but you need to make the effort to convert teh data to the proper data types.
here's one example, assuming your data is strings.
With mySampleData
AS
(
SELECT '200903021124' AsAlmostButNotQuiteADate UNION ALL
SELECT '200903030254'
)
SELECT CONVERT(datetime, STUFF(STUFF(AsAlmostButNotQuiteADate,11,0,':'),9,0,' ') + ':00.000')
FROM mySampleData

If they're strings, that's the way I do it except I don't add the + ':00.000'. It works just fine without that and it's one less character based operation (Concatenation, in this case).

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