SQL Function to convert date time to string

Question

Post reply

SQL Function to convert date time to string

kishorefeb28

Ten Centuries

Points: 1087
More actions
January 26, 2014 at 11:09 pm

#284790

Hi Experts,
please help me in writing a SQL Function as below
the input parameter for function should be datetime of sql datetimeformat
and out put should be a string = yyyymmdd1 or yyyymmdd2
the last character 1 or 2 based on below condition
if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2
please help me with such function am running out of time
thank you.
Kishore

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 1

USE Master

GO

DECLARE @dt DATETIME

SELECT @dt = '2014/01/27 01:00 PM'

select REPLACE(CONVERT(VARCHAR(10),@dt,111),'/','')+CASE WHEN DATEPART(HH,@dt) >= 6 AND DATEPART(HH,@dt) < 18 THEN '1' ELSE '2' END

SELECT @dt = '2014/01/27 01:00 AM'

select REPLACE(CONVERT(VARCHAR(10),@dt,111),'/','')+CASE WHEN DATEPART(HH,@dt) >= 6 AND DATEPART(HH,@dt) < 18 THEN '1' ELSE '2' END

Regards,
Mitesh OSwal
+918698619998

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

;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