Friday, September 14, 2012 10:21 PM
How to convert from yyyymmdd hh:mm:ss to yyyymmdd hh:00:000 ?
Friday, September 14, 2012 11:14 PMModerator
SELECT dateadd(hour, datediff(hour, 0, getdate()),0) SELECT dateadd(hour, datediff(hour, 0, yourDatetimeColumn),0) ... FROM....
- Marked As Answer by SQL_Gun Friday, September 14, 2012 11:38 PM
Friday, September 14, 2012 11:31 PMActually my datetime field is a Char(24) datatype, so I'm getting conversion failed error
Friday, September 14, 2012 11:37 PM
Got it. Thanks
dateadd(hour, datediff(hour, 0,(CAST(CONVERT(VARCHAR(23),Datefield,121) as DateTime))),0)
Saturday, September 15, 2012 2:22 PM
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
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
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Sunday, September 16, 2012 5:26 AM