Reputation: 13
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...
Upvotes: 1
Views: 451
Reputation: 8876
Try the following code:
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".
Upvotes: 0
Reputation: 26538
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
Upvotes: 0
Reputation: 2388
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.
Upvotes: 0
Reputation: 11387
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
Upvotes: 0
Reputation: 332791
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
Upvotes: 2