1

from the beginning. I extracted data from Sap to MYSQL DB. In some tables, there are columns that were extracted as FLOAT and looks like this:

20131009012152

As you can see it's like the string, but not float.

If I try to convert it into datetime, I get errors or overload etc.

I have tried CAST, CONVERT, SRT, SUBSTRING, nothing works.

Last try:

SELECT top 10 CREATED_AT, 
SUBSTRING(CAST(STR(CREATED_AT, 25, 5) as varchar), 1, 4)
from databank.tablename;
 -- wanted to substract parts (here, year) but I get just empty column as result.

Cast to nvarchar to datetime doesn't work. as well as nvarchar- bigint - datetime.

Hope, somebody can help me, thanks

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
verner
  • 11
  • 1
  • 4

3 Answers3

0

One option, rather ugly, uses the CONVERT function with a series of string concatenations. I first get your numeric timestamp over to a string using a CTE.

WITH cte AS (
    SELECT CAST(20131009012152 AS VARCHAR) num
)

SELECT
    CONVERT(datetime, SUBSTRING(num, 1, 4) + '-' + SUBSTRING(num, 5, 2) + '-' +
            SUBSTRING(num, 7, 2) + ' ' + SUBSTRING(num, 9, 2) + ':' +
            SUBSTRING(num, 11, 2) + ':' + SUBSTRING(num, 13, 2), 120) AS the_date
FROM cte;

The verbosity of this query and your data makes me think that you probably did not export properly. Use this only if SAP really has no ability to export a proper timestamp (which I doubt).

Output:

09.10.2013 01:21:52

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I can’t remove the MySQL tag for some reason. Can you? – Strawberry Nov 03 '17 at 08:17
  • @Strawberry Get ready to barf on the ugliest query of the day. – Tim Biegeleisen Nov 03 '17 at 08:31
  • unfortunately, I just got ready data. So, I cannot change export. If I try to use your code and use the column ---> CAST(my_column as varchar). I get again the error: Conversion failed when converting date and/or time from character string. – verner Nov 03 '17 at 09:44
  • Then maybe you don't even have numeric data. My demo works based on the information you provided in your question. Beyond this, you might want to spend some time researching how to export data from SAP. – Tim Biegeleisen Nov 03 '17 at 09:46
  • I wish I could. But I don't have access. If it would be so easy, I wouldn't ask such questions.....Anyway, I just have a table with column (float, null) and data like in the example. that's all. Nevertheless, thanks – verner Nov 03 '17 at 09:54
0

Found the similar way that can be directly used in SELECT:

CONVERT(datetime, 
SUBSTRING(CAST(STR(COLUMN, 15) as varchar), 1,5) + '-' + 
SUBSTRING(CAST(STR(COLUMN, 15) as varchar),6, 2) + '-' +
SUBSTRING(CAST(STR(COLUMN, 15) as varchar), 8, 2) + ' ' + 
SUBSTRING(CAST(STR(COLUMN, 15) as varchar),10, 2) + ':' +
SUBSTRING(CAST(STR(COLUMN, 15) as varchar), 12, 2) + ':' + 
SUBSTRING(CAST(STR(COLUMN, 15) as varchar), 14, 2), 120) as [COLUMN]

Result: 2017-01-01 15:33:15.000

verner
  • 11
  • 1
  • 4
0

Besides an old threat I want to share my solution:

Normally the SAP-Timestamps come like this in a char or varchar: 20220620081129

To bring it into a usable SQL format you need to stretch it into a date/datetime format with dashes and colons. This can be easily achieved by converting the text into a int/bigint and then formatting this number into an output format that looks like a date. Next step is just converting this resulting text into a datetime.

/*Code:*/ 
convert(datetime,format(convert(bigint,<fieldname>),'##-##-## ##:##:##'),120)

/*Example*/
DECLARE @SAPTable AS TABLE ([/BIC/XCREATE] VARCHAR(30))

INSERT INTO @SAPTable ([/BIC/XCREATE])
VALUES ('20220822144737');

SELECT convert(DATETIME, format(convert(BIGINT, [/BIC/XCREATE]), '##-##-## ##:##:##'), 120)
FROM @SAPTable

As easy as this. Just adjust the fieldname [/BIC/XCREATE] and the output format to your needs. In case you have other (shorter or longer) datetime values then, for sure, some things are needed to be adjusted but the principle is the same. At the end the result is a real date value in terms of the SQL-Server and can be used anyhow.

I think that way it's easier than fiddling with substrings.

dusfor63
  • 21
  • 3