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

So for example myCol might have a value of '1900-01-01 00:30:00.000' and i want to increase the time part by 20% (*1.2) so i want the return value to be '1900-01-01 00:36:00.000' — Alex123
– Alex123, Commented Nov 3, 2009 at 5:30

OMG Ponies · Accepted Answer · 2009-11-03 05:34:24Z

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

answered Nov 3, 2009 at 5:34

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jimconstable · Accepted Answer · 2009-11-03 05:47:27Z

0

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.

answered Nov 3, 2009 at 5:47

jimconstable

2,39819 silver badges28 bronze badges

Comments

anishMarokey · Accepted Answer · 2009-11-03 05:39:11Z

0

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

answered Nov 3, 2009 at 5:39

anishMarokey

11.4k2 gold badges36 silver badges47 bronze badges

Comments

priyanka.sarkar · Accepted Answer · 2009-11-03 05:49:36Z

0

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

answered Nov 3, 2009 at 5:49

priyanka.sarkar

26.7k43 gold badges133 silver badges182 bronze badges

Comments

C B · Accepted Answer · 2017-10-24 19:47:58Z

0

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".

edited Oct 24, 2017 at 19:47

C B

1,7176 gold badges20 silver badges20 bronze badges

answered Nov 3, 2009 at 5:21

Himadri

8,89616 gold badges50 silver badges70 bronze badges

1 Comment

Alex123 Over a year ago

I actually want to return a valid DataTime, your query returns 2 columns of type double

Collectives™ on Stack Overflow

SQL Date function question

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related