Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Function to convert date time to string Expand / Collapse
Author
Message
Posted Sunday, January 26, 2014 11:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
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
Post #1534847
Posted Sunday, January 26, 2014 11:44 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646
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
Post #1534853
Posted Monday, January 27, 2014 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
;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
Exploring Recursive CTEs by Example Dwain Camps
Post #1534893
Posted Monday, January 27, 2014 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
It Worked

Thanks a ton Mitesh
Post #1534897
Posted Monday, January 27, 2014 2:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
Thank you for the reply chris.
Post #1534899
Posted Monday, January 27, 2014 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
--EDIT--

It is Monday morning and I misread this post entirely. Need more coffee.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1535018
Posted Tuesday, January 28, 2014 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 6, Visits: 209
Greetings!

Would using date type 112 not remove the need to replace the "/" characters in the date string?

This would reduce the date string portion from

REPLACE(CONVERT(VARCHAR(10),@dt,111),'/','')

to

CONVERT(VARCHAR(8),@dt,112)

Post #1535652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse