Convert string containing ''yyyyMMdd_HHmm' back to Datetime format (dd-mm-yyyy

Question

Convert string containing ''yyyyMMdd_HHmm' back to Datetime format (dd-mm-yyyy

Weegee71

SSChasing Mays

Points: 604
More actions
June 8, 2021 at 10:23 am

#3893328

Hi there
I have a database table containing historical data
This table contains the name of tables which have the datetime formatted as ''yyyyMMdd_HHmm'
For example
SIData_111111_01_20210607_1722
SIData_111111_01_20210607_1715
SIData_111111_01_20210607_1112
Is there a function or other way that I can use to extract the datetime format from this
and assign this to a datetime object?

For example from the variable SIData_111111_01_20210607_1722
extract out '20210607_1722' and display that as '07-06-2021 17:22:00'

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

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

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

You could try SMALLDATETIMEFROMPARTS

select v.input, 
       smalldatetimefromparts(substring(v.input, 18, 4),
                              substring(v.input, 22, 2),
                              substring(v.input, 24, 2),
                              substring(v.input, 27, 2),
                              substring(v.input, 29, 2)) dt_from_parts
from (values ('SIData_111111_01_20210607_1722')) v(input);

inputdt_from_parts
SIData_111111_01_20210607_17222021-06-07 17:22:00

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Thanks Steve

Thats looks good

OK if I wanted to run that code through a select statement (where my download tables are)

Then how I would i run it?

My source Select Statement is

select LoggerDownloadTableName from LoggerChannelDownload lcd

Would i do a cross apply?

Steve Collins SSCrazy Points: 2068 More actions · Answer 3

The function could be included in the SELECT list

select lcd.LoggerDownloadTableName,
       smalldatetimefromparts(substring(lcd.LoggerDownloadTableName, 18, 4),
                              substring(lcd.LoggerDownloadTableName, 22, 2),
                              substring(lcd.LoggerDownloadTableName, 24, 2),
                              substring(lcd.LoggerDownloadTableName, 27, 2),
                              substring(lcd.LoggerDownloadTableName, 29, 2)) dt_from_parts
from LoggerChannelDownload lcd;

Alternately, you could use CROSS APPLY. It makes the SELECT list more readable imo

select lcd.LoggerDownloadTableName, v.dt_from_parts
from LoggerChannelDownload lcd
     cross apply (values (smalldatetimefromparts(substring(lcd.LoggerDownloadTableName, 18, 4),
                                                 substring(lcd.LoggerDownloadTableName, 22, 2),
                                                 substring(lcd.LoggerDownloadTableName, 24, 2),
                                                 substring(lcd.LoggerDownloadTableName, 27, 2),
                                                 substring(lcd.LoggerDownloadTableName, 29, 2)))) 
                                                 v(dt_from_parts);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Weegee71 SSChasing Mays Points: 604 More actions · Answer 4

Fantastic. thank you so much for that Steve

I had an inital workaround on this...but it was much more complicated and longer.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

Another way... the formula in the CROSS APPLY converts the substring to a DATETIME2 just in case you want to see how to do that. The WHERE clause ensures that you're only looking at table names that can actually be converted. And, finally, please get into the habit of providing readily consumable test data like I have done in the first snippet below. It helps those trying to help you.

--===== Create a test table with test data on-the-fly.
     -- THIS IS NOT A PART OF THE SOLUTION!
   DROP TABLE IF EXISTS #TestTable; 
 SELECT v.*
   INTO #TestTable
   FROM (VALUES
         ('SIData_111111_01_20210607_1722')
        ,('SIData_111111_01_20210607_1715')
        ,('SIData_111111_01_20210607_1112')
        )v(TableName)
;
--===== Solution Example
 SELECT  OriginalTableName = TableName
        ,DesiredOutput = CONVERT(CHAR(11),d.dt,103)+CONVERT(CHAR(8),d.dt,108)
   FROM #TestTable
  CROSS APPLY (SELECT CONVERT(DATETIME2(0),STUFF(REPLACE(RIGHT(TableName,13),'_',' '),12,0,':')))d(DT)
  WHERE TableName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]'
;

Results:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 6

SELECT  data,
        CONVERT(CHAR(10), CAST(SUBSTRING(data, 18, 8) AS DATETIME), 105) + ' ' + SUBSTRING(data, 27, 2) + ':' + SUBSTRING(data, 29, 2) + ':00' AS [Awkward (as it is still a string)],
        CAST(SUBSTRING(data, 18, 8) + ' ' + SUBSTRING(data, 27, 2) + ':' + SUBSTRING(data, 29, 2) + ':00' AS DATETIME2(0)) AS [Fine (as it is a date)],
        SUBSTRING(data, 18, 8) + ' ' + SUBSTRING(data, 27, 2) + ':' + SUBSTRING(data, 29, 2) AS [Better (as it is will do implicit conversion)]
FROM    (
                VALUES  ('SIData_111111_01_20210607_1722'),
                        ('SIData_111111_01_20210607_1715'),
                        ('SIData_111111_01_20210607_1112')
        ) AS d(data);

N 56°04'39.16"
E 12°55'05.25"