CODEWITHSUNDEEP
sqldatetime
user2210516
user2210516

Reputation: 683

Turn string with date and time to hh:mm

I have a string field in my table with date and time. Like this

20131001094049

Now i have to get the hour and Minute out of this like 09:40.

How can i easily get that?

Upvotes: 0

Views: 151

Answers (2)

Chris Morledge
Chris Morledge

Reputation: 71

Because your date and time information is not stored in a standard DateTime format for direct conversion, you will need to rely on string manipulation i.e:

    SELECT SUBSTRING('20131001094049', 9, 2) + ':' + 
           SUBSTRING('20131001094049', 11, 2)

To return a full DateTime type you could manipulate the whole string and use the FORMAT function (available in SQL Server 2012 or greater):

DECLARE @dt nvarchar(50), @newdt nvarchar(50)
SET @dt = '20131001094049'

SET @newdt = SUBSTRING(@dt, 1, 4) + '-' + SUBSTRING(@dt, 5, 2) + '-' + SUBSTRING(@dt, 7, 2) + ' ' + SUBSTRING(@dt, 9, 2) + ':' + SUBSTRING(@dt, 11, 2) + ':' + SUBSTRING(@dt, 13, 2)
SELECT FORMAT(CONVERT(datetime, @newdt), 'HH:mm')

Both will return 09:40

Upvotes: 2

Elias
Elias

Reputation: 2632

I am going to assume, based on your string that the format is YYYYMMDDHHMMSS (Year,Month,Day,Hour,Minute,Second). This solution is for SQL Server.

Considering that you could go for the poor man fix of string manipulation.

SELECT 
    LEFT(RIGHT(20131001094049, 6 ) , 2) + ':' + 
    RIGHT(LEFT(RIGHT(20131001094049, 6 ) , 4),2)

Which returns 09:40

What happens is it takes the original date, 20131001094049, takes the right six characters 094049 and then the left two 09. Then it concatenates that to a : and then concatenates the resulting string to 04 of the assumed MM(Minutes) section.

Upvotes: 2

Related Questions