RE: SQL Function to convert date time to string

ChrisM@Work

SSC Guru

Points: 186127

January 27, 2014 at 2:06 am

#1683451

;WITH SampleData AS (

SELECT MyDate = CAST('2014-01-27 05:59:59.997' AS DATETIME) UNION ALL

SELECT '2014-01-27 06:00:00.000' UNION ALL

SELECT '2014-01-27 06:00:00.003' UNION ALL

SELECT '2014-01-27 17:59:59.994' UNION ALL

SELECT '2014-01-27 17:59:59.997' UNION ALL

SELECT '2014-01-27 18:00:00.000')

SELECT

MyDate,

CONVERT(CHAR(8),MyDate, 112) + CASE WHEN DATEPART(HOUR,MyDate) >= 6 AND DATEPART(HOUR,MyDate) < 18 THEN '1' ELSE '2' END

FROM SampleData

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden