Beginning of the Minute and Second of Datetime

Posted: May 19, 2017 | Author: | Filed under: Uncategorized | Leave a comment 
  

create table test (StartDatetime datetime);


insert into  test values ('2017-10-03 00:11:01.000'),('2017-10-03 00:20:02.333'),
('2017-10-03 00:33:03.333'),
('2017-10-03 00:44:04.667'),
('2017-10-03 00:55:00.333'),
('2017-10-03 00:06:01.697')
 

 select  
  dateadd(mi, datediff(mi, 0, CURRENT_TIMESTAMP), 0) beginningOfTheMinute_current,
 dateadd(mi, datediff(mi, 0, StartDatetime), 0) beginningOfTheMinute1 
 , dateadd(minute,datediff(minute,Cast(Cast(StartDatetime as date) as datetime), StartDatetime)
 ,Cast(Cast(StartDatetime as date) as datetime)) beginningofminute2
 
  , dateadd(second,datediff(second,Cast(Cast(StartDatetime as date) as datetime), StartDatetime)
 ,Cast(Cast(StartDatetime as date) as datetime)) beginningofsecond

  from test


drop table test 

--https://jingyangli.wordpress.com/?s=beginning