extract days,hour,min,sec

Question

Post reply

extract days,hour,min,sec

Mad-Dog

SSCertifiable

Points: 6117
More actions
January 23, 2010 at 9:01 am

#215769

Hi,
how can i extract the days,hour,min,sec from a datetime value when it compare to current date like this example?(final results like that
27 Days, 7 Hours, 19 Minutes, 7 Seconds)
THX
DECLARE @SQL_START_TIME DATETIME
DECLARE @CURRENT_DATETIME DATETIME
SET @CURRENT_DATETIME = GETDATE()
SELECT @SQL_START_TIME = create_date FROM master.sys.databases where name = 'tempdb'
SELECT datediff(dd,@SQL_START_TIME,@CURRENT_DATETIME)

Viewing 10 posts - 1 through 9 (of 9 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Look into the DATEPART function. That may be what you need.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Mad-Dog (1/23/2010)
Hi,
how can i extract the days,hour,min,sec from a datetime value when it compare to current date like this example?(final results like that
27 Days, 7 Hours, 19 Minutes, 7 Seconds)
THX
DECLARE @SQL_START_TIME DATETIME
DECLARE @CURRENT_DATETIME DATETIME
SET @CURRENT_DATETIME = GETDATE()
SELECT @SQL_START_TIME = create_date FROM master.sys.databases where name = 'tempdb'
SELECT datediff(dd,@SQL_START_TIME,@CURRENT_DATETIME)

Homebrew has the right idea but let me ask... why do you need to do this? What is the business reason for this?

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

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 3

I'm working on a report info.

can you give me an example of ths datepart?

THX

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

select datepart(hour, getdate()) -- Get just the hour ... in "military" hours

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 5

Thanks but i found another way

DECLARE @TEMP_TABLE_HOLD_TOTAL_SECOND TABLE (TOTAL_SECOND BIGINT)

DECLARE @SQL_START_TIME DATETIME

DECLARE @CURRENT_DATETIME DATETIME

SET @CURRENT_DATETIME = GETDATE()

SELECT @SQL_START_TIME = create_date FROM master.sys.databases where name = 'tempdb'

INSERT INTO @TEMP_TABLE_HOLD_TOTAL_SECOND

SELECT DATEDIFF (ss,@SQL_START_TIME,@CURRENT_DATETIME)AS 'TOTAL_SECOND'

select cast((TOTAL_SECOND / 86400)as nvarchar (5)) + ' Days, '

+ cast((TOTAL_SECOND % 86400) / 3600 as nvarchar (5)) + ' Hours, '

+ cast((TOTAL_SECOND % 3600) / 60 as nvarchar (5)) + ' Minutes, '

+ cast(TOTAL_SECOND % 60 as nvarchar (5)) + ' Seconds'

from @TEMP_TABLE_HOLD_TOTAL_SECOND

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

I'd say you've made it hard on yourself...

SELECT STR(DATEDIFF(dd,0,GETDATE()-@Sql_Start_Time),5) + ' Days, '

+ DATENAME(hh,GETDATE()-@Sql_Start_Time) + ' Hours, '

+ DATENAME(mi,GETDATE()-@Sql_Start_Time) + ' Minutes, '

+ DATENAME(ss,GETDATE()-@Sql_Start_Time) + ' Seconds '

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

lisbon SSC Enthusiast Points: 102 More actions · Answer 7

Even simpler:

cast(datediff(dd,0,getdate()) as datetime)

lx

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

lisbon (1/25/2010)
Even simpler:
cast(datediff(dd,0,getdate()) as datetime)
lx

Not exactly what the op asked for but I agree.

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

nigel. SSChampion Points: 11636 More actions · Answer 9

Mad-Dog (1/23/2010)
I'm working on a report info.

An SQL Reporting Services report, a Crystal Report, or something else...?

It usually better to do this sort of thing in the reporting tool itself.

Nigel
How to post questions