SQL Date function question

Question

I'm using SQL Server 2005 and I have a DateTime column.

How can I multiply the hour and minute of a DateTime column by 1.2 and return the new value

So something like:

SELECT 
MyColMinute = DATEPART(minute, myCol) * 1.2
MyColHour = DATEPART(second, myCol) * 1.2,
MyCol
From NyTable

Of course the above won't work!

So for example myCol might have a value of '1900-01-01 00:30:00.000'. I want to increase the time portion by 20% (*1.2), so I want the return value to be '1900-01-01 00:36:00.000'. I actually want to return a valid DataTime, not the minutes & seconds as separate values...

Answer 1

Try the following:

SELECT DATEADD(SECOND,DATEPART(second, '2001-01-23 01:02:50') * 1.2, DATEADD(MINUTE,DATEPART(minute, '2001-01-23 01:02:50') * 1.2,'2001-01-23 01:02:50'))

you can replace '2001-01-23 01:02:50' with your column and add " from yourtable".

Answer 2

This:

SELECT DATEADD(n, 
               DATEPART(minute, t.column) * 1.2, 
               DATEADD(s, DATEPART(second, t.column) * 1.2, t.column))
  FROM TABLE t

...adds the time based on the original datetime value. If you want the minutes to be added on top of the seconds value, use:

SELECT DATEADD(n, DATEPART(minute, DATEADD(s, DATEPART(second, t.column) * 1.2, t.column)) * 1.2, DATEADD(s, DATEPART(second, t.column) * 1.2, t.column))
  FROM TABLE t

Answer 3

actually you missed one comma in MyColMinute

Try this

select 
DATEPART(minute, myCol) * 1.2 as MyColMinute,
 DATEPART(second, myCol) * 1.2 as MyColHour ,
MyCol
From NyTable

Answer 4

SELECT 
DATEADD(
    s,
    DATEDIFF(s,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0),GETDATE())*1.2,
    DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
)

Take the number of seconds in the day so far, multiply it by 1.2 and then add it back to the day from midnight. If you want more granularity I guess you could use ms for milliseconds.

Answer 5

declare @tbl table(dates datetime)
insert into @tbl 
select '1900-01-01 00:30:00.000' union all select '2009-11-03 12:04:56.370' union all
select '2009-11-03 09:08:51.380' 

select OriginalDates = dates
     ,MyColMinute=DATEADD(mi, DATEPART(mi,dates) * 1.2, dates)
     ,MyHourMinute = DATEADD(hh, DATEPART(hh,dates) * 1.2, dates)
from @tbl