Converting Date Time to Minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Date Time to Minutes

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-21 : 13:06:13

Hi,

I have a database column in datetime datatype . From the datetime column I applied the formula :

substring(convert(varchar(10),[Received time],108),1,5)

which gives me values like 06:00 (HH:MM)

I need to get the minutes from the HH:MM like 06:00 - 360 minutes ( numeric) as I will later be required to do an average of the minutes.

I need to convert HH:MM to represent only minutes in Numeric. Please let me know on this.

Thank You.

Dp

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-21 : 13:13:04

Try:

select Datediff(mi,convert(datetime,'00:00',108), convert(datetime,<substitue your return value here>,108))

Example:
select Datediff(mi,convert(datetime,'00:00',108), convert(datetime,'06:00',108))

There are other simple ways too..

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-21 : 13:46:06

Can you leave the datatype as datetime during all your caclulations then format the results in your frontend report? Or at least in the final select. In other words if you want to subtract 360 minutes then use: DATEADD(minute, -360, [Received time])

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-21 : 15:43:18

This should do what you want, provided all your dates are before 5836-01-01.


select
	DateTimeMinutes =  datediff(mi,'17530101',a.MyDate)%1440 ,
	a.*
from
	( -- Test Data
	select MyDate = getdate() union all
	select MyDate = '17531231 13:57:00'	union all
	select MyDate = '20111231 06:00:00'	union all
	select MyDate = '56781231 06:00:00'	union all
	select MyDate = '22221231 23:59:59'
	) a

Results:

DateTimeMinutes MyDate
--------------- -----------------------
            946 2010-10-21 15:46:14.477
            837 1753-12-31 13:57:00.000
            360 2011-12-31 06:00:00.000
            360 5678-12-31 06:00:00.000
           1439 2222-12-31 23:59:59.000

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-22 : 06:01:43

Somewhat cleaner and accepts full datetime range

SELECT	DATEDIFF(MINUTE, 0, DATEADD(DAY, DATEDIFF(DAY, MyDate, 0), MyDate)) AS DateTimeMinutes,
	MyDate
FROM	(
		SELECT	GETDATE() UNION ALL
		SELECT	'17531231 13:57:00' UNION ALL
		SELECT	'20111231 06:00:00' UNION ALL
		SELECT	'56781231 06:00:00' UNION ALL
		SELECT	'99991231 23:59:59'
	) AS d(MyDate)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources