You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand:
DECLARE @datestring NVARCHAR(20) = '20120225143620'
-- desired format: '20120225 14:36:20'
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
SELECT CONVERT(DATETIME, @datestring) AS FormattedDate
Output:
FormattedDate
=======================
2012-02-25 14:36:20.000
This approach will work if your string is always the same length and format, and it works from the end of the string to the start to produce a value in this format: YYYYMMDD HH:MM:SS
For this, you don't need to separate the date portion in anyway, as SQL Server will be able to understand it as it's formatted.
Related Reading:
STUFF (Transact-SQL)
Answer from Tanner on Stack OverflowThe STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
Top answer 1 of 3
44
You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand:
DECLARE @datestring NVARCHAR(20) = '20120225143620'
-- desired format: '20120225 14:36:20'
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
SELECT CONVERT(DATETIME, @datestring) AS FormattedDate
Output:
FormattedDate
=======================
2012-02-25 14:36:20.000
This approach will work if your string is always the same length and format, and it works from the end of the string to the start to produce a value in this format: YYYYMMDD HH:MM:SS
For this, you don't need to separate the date portion in anyway, as SQL Server will be able to understand it as it's formatted.
Related Reading:
STUFF (Transact-SQL)
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
2 of 3
1
DECLARE @datestring NVARCHAR(20) = '20220822190000330';
DECLARE @dateint BIGINT = CONVERT(BIGINT,@datestring);
SELECT CONVERT(DATETIME,FORMAT(@dateint, '####-##-## ##:##:##:###')) AS FormatDate;
Output
FormatDate
-----------------------
2022-08-22 19:00:00.330
Top answer 1 of 2
6
Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.
Something like this:
declare @S varchar(50)
set @S = '20120606122012'
select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)
2 of 2
6
You could also add this as a computed column or make it part of a view, so that you don't have to perform the calculation over and over in every query.
As a computed column:
ALTER TABLE dbo.whatever ADD dt
AS CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'), 120);
Since the calculation is deterministic, it can be persisted and/or indexed.
Using a view:
CREATE VIEW dbo.view_whatever
AS
SELECT /* other columns, */
dt = CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'))
FROM dbo.whatever;
Then you can simply say:
WHERE dt < DATEADD(MINUTE, -20, CURRENT_TIMESTAMP)
Though I have to agree with the others - you need to fix the model. There is no advantage whatsoever to storing this as a string, except that your users don't get pesky error messages when they enter non-date junk like whosawatsa or even 20120230000000.
how can i convert 'yyyymmddhhmmss' to datetime - SQL Server Forums
Microsoft SQL Server articles, forums and blogs for database administrators (DBA) and developers. More on sqlteam.com
Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS – SQLServerCentral Forums
Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS Forum – Learn more on SQLServerCentral More on sqlservercentral.com
Date format YYYYMMDDHHMMSS - Databases & Queries - Spiceworks Community
Dear Everyone Can somebody help me to convert a date/time values into a format YYYYMMDDHHMMSS Currently, when we select current date, it would result to:- 2005-02-08 15:15:34.247 We would like if possible to have the output formatted without the “”-“” & “”:“”. Though we can ... More on community.spiceworks.com
0
February 8, 2005How to get YYYYMMDDHHMMSS – SQLServerCentral Forums
How to get YYYYMMDDHHMMSS Forum – Learn more on SQLServerCentral More on sqlservercentral.com
Videos
Convert yyyymmddhhmmss String to SQL Server Datetime ...
01:24
How to Convert a YYYYMMDDhhmmss String to Datetime in SQL Server?
09:05
SQL | How to Convert Date / Time Formats ? | Convert | Format - ...
How to Format Dates in SQL Server using Convert and Format
02:53
sql date conversion | Convert Date format into DD/MM/YYYY format ...
MSSQLTips
mssqltips.com › home › sql date format examples using convert function
SQL Date Format Examples using SQL CONVERT Function
September 26, 2025 - --SELECT a datetime column as a string formatted dd/mm/yyyy (4 digit year) SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 103) ExpectedDeliveryDateFormattedAsText FROM Purchasing.PurchaseOrders WHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted dd/mm/yy (2 digit year) SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 3) ExpectedDeliveryDateFormattedAsText FROM Purchasing.PurchaseOrders WHERE OrderDate < @Datetime; Once the query runs, review SQL CONVERT output as shown below.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › cast-and-convert-transact-sql
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
SQL Server returns an error message when converting nonnumeric char, nchar, nvarchar, or varchar data to decimal, float, int, numeric. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal. The styles for which the string-to-datetime conversion is nondeterministic are as follows:
Databricks Community
community.databricks.com › t5 › data-engineering › string-converstion-to-datetimestamp-format › td-p › 27940
Solved: String converstion to datetimestamp format - Databricks Community - 27940
October 12, 2022 - 1) what expression i can use to ...nguage-manual/sql-ref-datetime-pattern.html#pat... spark.sql("select date_format(to_timestamp(concat('20221009','010911'),'yyyyMMddHHmmss'),'yyyy-MM-dd hh:mm:ss a') as my_date").display()...
SQL Team
sqlteam.com › forums › topic.asp
how can i convert 'yyyymmddhhmmss' to datetime - SQL Server Forums
July 15, 2010 - Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Celonis
community.celonis.com › general-product-q-a-2 › how-to-convert-string-format-yyyymmddhhmmss-into-the-timestamp-date-value-using-either-sql-or-pql-130
How to convert string(format YYYYMMDDHHMMSS) into the timestamp(date) value using either SQL or PQL? | Community
September 9, 2022 - How to convert string(format YYYYMMDDHHMMSS) into the timestamp(date) value using either SQL or PQL?
SQLServerCentral
sqlservercentral.com › forums › topic › adding-yyyymmdd-and-hhmmss-to-get-mmddyyyy-hhmmss
Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS – SQLServerCentral Forums
June 12, 2014 - wheresjh.run_date = CONVERT (VARCHAR (8), GetDate(), 112) and sjh.step_id = 0 and run_status in (0, 1)
Spiceworks
community.spiceworks.com › programming & development › databases & queries
Date format YYYYMMDDHHMMSS - Databases & Queries - Spiceworks Community
February 8, 2005 - Dear Everyone Can somebody help ... without the “”-“” & “”:“”. Though we can only do this:- select CONVERT(char(8), getdate(), 112) & would result to 20050208....
SQL Shack
sqlshack.com › sql-convert-date-functions-and-formats
SQL Convert Date functions and formats
May 21, 2021 - Similarly, lets us add 1 year to current date using the following query. We can combine the SQL DATEADD and CONVERT functions to get output in desired DateTime formats. Suppose, in the previous example; we want a date format in of MMM DD, YYYY.
SQLServerCentral
sqlservercentral.com › forums › topic › how-to-get-yyyymmddhhmmss
How to get YYYYMMDDHHMMSS – SQLServerCentral Forums
October 21, 2021 - SELECT YYYYMMDDHHMISS = CONVERT(CHAR(14), CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000 +DATEPART(hh,GETDATE())*10000 +DATEPART(mi,GETDATE())*100 +DATEPART(ss,GETDATE()) ) ; Yeah... I know what a lot of folks are thinking. "Big deal! 3.6 seconds v.s. 10.4 on 10 MILLION rows!
Codesenior
codesenior.com › sources › docs › tutorials › How-to-convert-YYYYMMDDHHMMSS-to-Datetime-in-Microsoft-Sql-Server.pdf pdf
How to convert YYYYMMDDHHMMSS to Datetime in Microsoft Sql Server
How to convert YYYYMMDDHHMMSS to Datetime in Microsoft Sql Server · To convert yyyymmddhhmmss string format to datetime in Microsoft Sql Server, you can use below sql script:
Top answer 1 of 8
74
Since SQL Server Version 2012 you can use:
CopySELECT format(getdate(),'yyyyMMddHHmmssffff')
2 of 8
22
This seems to work:
Copydeclare @d datetime
set @d = '2014-04-17 13:55:12'
select replace(convert(varchar(8), @d, 112)+convert(varchar(8), @d, 114), ':','')
Result:
Copy20140417135512
SQL Shack
sqlshack.com › sql-server-functions-for-converting-string-to-date
SQL Server functions for converting a String to a Date
May 21, 2021 - Explicit where conversions are visible to the user and they are performed using CAST or CONVERT functions or other tools · In this article, we will explain how a string to date conversion can be achieved implicitly, or explicitly in SQL Server using built-in functions such as CAST(), TRY_CAST(), CONVERT(), TRY_CONVERT() and TRY_PARSE().
Google Cloud
googlecloudcommunity.com › looker › q&a › looker
Solved: Re: How to convert string to timestamp in looker
April 1, 2022 - Hi team, We have date string as 20210131120101(YYYYMMDDhhmmss) in table. We want to interpret this string into timestamp so looker can treat it as time stamp. Then we want to extract date parts from 20210131120101: 20…
Databricks Community
community.databricks.com › t5 › data-engineering › convert-date-to-yyyymmdd-in-databricks-sql › td-p › 13160
Solved: Convert Date to YYYYMMDD in databricks sql - Databricks Community - 13160
April 10, 2024 - ... basically with to_date() and date_format() you can play with dates all you want. to_date converts a string to a date (with the format the datestring is in as a parameter), and date_format converts a date to a string (with the format the output has to be in as a parameter).
Oracle
forums.oracle.com › ords › apexds › post › how-to-convert-datetime-to-yyyy-mm-ddthh-mi-ss-000-0000-so-2375
How to convert DateTime to YYYY-MM-DDTHH:MI:SS.000+0000 so that I can get records from table - Oracle Forums
April 28, 2021 - Hi, I looking for a help in converting the Timestamp to a string in the format YYYY-MM-DDTHH:MI:SS.000+0000 Background: I have a column Opt_Out_Time defined as a string and that stores the values in &...
Informatica Network
network.informatica.com › s › question › 0D56S0000AD70FbSQJ › help-to-convert-string-into-date
help to convert string into date
Loading · ×Sorry to interrupt · Refresh · Actions · Ask a Question · Contact Support · Terms of Use · Trademarks · Question Detail
Microsoft Learn
learn.microsoft.com › en-us › dotnet › standard › base-types › custom-date-and-time-format-strings
Custom date and time format strings - .NET | Microsoft Learn
Dim thisDate1 As Date = #6/10/2011# Console.WriteLine("Today is " + thisDate1.ToString("MMMM dd, yyyy") + ".") Dim thisDate2 As New DateTimeOffset(2011, 6, 10, 15, 24, 16, TimeSpan.Zero) Console.WriteLine("The current date and time: {0:MM/dd/yy H:mm:ss zzz}", thisDate2) ' The example displays the following output: ' Today is June 10, 2011. ' The current date and time: 06/10/11 15:24:16 +00:00 · In parsing operations, custom date and time format strings can be used with the DateTime.ParseExact, DateTime.TryParseExact, DateTimeOffset.ParseExact, and DateTimeOffset.TryParseExact methods.
Microsoft Fabric Community
community.fabric.microsoft.com › t5 › Desktop › Convert-YYYYMMDDHHMMSS-into-DD-MM-YYYY › td-p › 3237305
Solved: Convert YYYYMMDDHHMMSS into DD.MM.YYYY - Microsoft Fabric Community
May 23, 2023 - Solved: Hello there, a field called "lastseen" contains a String which represents a date in the format YYYYMMDDHHMMSS. For example