yyyymmdd hh:mm:ss to yyyymmdd hh:00:000

Question

Hi,

How to convert from yyyymmdd hh:mm:ss to yyyymmdd hh:00:000 ?

Example
date
2012-09-07 12:28:25.910

Output
2012-09-07 12:00:00:000

Answer 1

SELECT dateadd(hour, datediff(hour, 0, getdate()),0) 
SELECT dateadd(hour, datediff(hour, 0, yourDatetimeColumn),0)  ... FROM....

Answer 2

Actually my datetime field is a Char(24) datatype, so I'm getting conversion failed error

Answer 3

Got it. Thanks

dateadd(hour, datediff(hour, 0,(CAST(CONVERT(VARCHAR(23),Datefield,121) as DateTime))),0)

Answer 4

Actually my datetime field is a Char(24) datatype, so I'm getting conversion failed error

Find the idiot that did this, and fire him. It is the wrong data type and it is an absurd length.  Create a look-up table using the TIME data type; it will be handy for other things. Do not use the old CONVERT() string function; you want to have temporal data  and not strings. Also, we have CURRENT_TIMESTAMP now, so stop using the old getdate() from UNIX and Sybase. 

CREATE TABLE Timeslots
(start_timeslot TIME(0) NOT NULL,
  end_timeslot TIME(0) NOT NULL,
 CHECK (start_timeslot < end_timeslot));

INSERT INTO Timeslots
VALUES 
('00:00:00', '00:59:59'),
('01:00:00', '01:59:59'),
 ..
('23:00:00', '23:59:59');

Now split out the date part with teh DATE data type and add the appropriate time slot to it. 

SELECT CAST (mess AS DATE) + start_timeslot
  FROM Timeslots AS S
 WHERE CAST (stinking_mess AS TIME(0))
    BETWEEN S.start_timeslot AND S.end_timeslot);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL