I have a varchar column that provides datetimes in the following format
yyyymmddhhmmss
In order to store this in a datetime2 column I need to convert it to ANSI format
yyyy-mm-dd hh:mm:ss
There is a convert function I have used for similar columns formatted in UK format e.g. 04/10/2023 11:15:00 can be solved with CONVERT(datetime2(0),COLUMN,103).
The issue is I face an error when attempting this with
SELECT TOP (5)
[HistoryID]
,[CheckedBy]
,[CheckedTime]
,CheckedTime2 = SUBSTRING(CheckedTime,1,4) + '-' + SUBSTRING(CheckedTime,5,2) + '-' + SUBSTRING(CheckedTime,7,2) + ' ' + SUBSTRING(CheckedTime,9,2) + ':' + SUBSTRING(CheckedTime,11,2) + ':' + SUBSTRING(CheckedTime,13,2)
--,CONVERT(datetime2(0),CheckedTime,112)
,CONVERT(datetime2(0),
SUBSTRING(CheckedTime,1,4) + '-' + SUBSTRING(CheckedTime,5,2) + '-' + SUBSTRING(CheckedTime,7,2) + ' ' + SUBSTRING(CheckedTime,9,2) + ':' + SUBSTRING(CheckedTime,11,2) + ':' + SUBSTRING(CheckedTime,13,2)
,120) AS [CheckedTime3]
,[RevokedBy]
,[RevokedTime]
,[AnalystRef]
FROM [staging].[AccuracyChecks]The above generates the following
| HistoryID | CheckedBy | CheckedTime | CheckedTime2 | CheckedTime3 |
|---|---|---|---|---|
| 63061C5F-0062-02056B95 | 1 | 20220825082057 | 2022-08-25 08:20:57 | 2022-08-25 08:20:57 |
| 63061C5F-0062-02056B96 | 1 | 20220825082057 | 2022-08-25 08:20:57 | 2022-08-25 08:20:57 |
| 63061CBC-0062-02056B97 | 1 | 20220825082053 | 2022-08-25 08:20:53 | 2022-08-25 08:20:53 |
| 63061CBC-0062-02056B98 | 1 | 20220825082053 | 2022-08-25 08:20:53 | 2022-08-25 08:20:53 |
| 63061E55-0062-02056B99 | 1 | 20220825082026 | 2022-08-25 08:20:26 | 2022-08-25 08:20:26 |
As you can see when I use the substring method or convert with the substring method, it generates it correctly.
The commented-out line
CONVERT(datetime2(0),CheckedTime,112)produces the following error
Conversion failed when converting date and/or time from character string.
The only difference I can see in the docs is the style code 112 does not include TIME data.
What would be the correct way to convert to the required format as using the SUBSTRING method causes a different error related to a filter which I have previously had help resolving through the CONVERT function.
Although I doubt it matters, I have no control over the format I receive the data in.
1条答案
按热度按时间j2qf4p5b1#
So in answer to my original question, it doesn't appear that there is a way to use CONVERT with it in that format style (yyyymmddhhmmss).
Upon suggestion in the comments, I added a new datetime2 column to the staging table. Using this new column, I altered the SP to update that column using the original SUBSTRING method, only for the filtered records I was working on at that time. I was able to then use the newly created column in the MERGE statement and this avoided the order of operation error seen before.