CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Alex123
Alex123

Reputation: 13

SQL Date function 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...

Upvotes: 1

Views: 451

Answers (5)

Himadri
Himadri

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

priyanka.sarkar
priyanka.sarkar

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

jimconstable
jimconstable

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

anishMarokey
anishMarokey

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

OMG Ponies
OMG Ponies

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

Related Questions