Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime

by

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 )

More Related Contents:

  1. How to convert SQL Server’s timestamp column to datetime format
  2. How can I truncate a datetime in SQL Server?
  3. What is the significance of 1/1/1753 in SQL Server?
  4. How to get Time from DateTime format in SQL?
  5. How to get first and last day of previous month (with timestamp) in SQL Server
  6. How to remove the time portion of a datetime value (SQL Server)?
  7. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  8. SQL Update with row_number()
  9. SQL Query to find the last day of the month
  10. Is a single SQL Server statement atomic and consistent?
  11. Connecting to MS SQL Server with Windows Authentication using Python?
  12. Handling date in SQL Server
  13. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  14. Should every User Table have a Clustered Index?
  15. cross apply xml query performs exponentially worse as xml document grows
  16. Comparing dates stored as varchar
  17. Add default value of datetime field in SQL Server to a timestamp
  18. SQL Server Configuration Manager cannot be found
  19. Best way to store time (hh:mm) in a database
  20. Retrieving the date in SQL Server; CURRENT_TIMESTAMP vs GetDate()
  21. SQL Server Group by Count of DateTime Per Hour?
  22. Compute MD5 hash of a UTF8 string
  23. Why SQL Server DATETIME type saves time in ticks of 1/300 of a sec?
  24. Sql Server replication requires the actual server name to make a connection to the server
  25. SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]
  26. Convert from DateTime to INT
  27. How does SQL Server decide format for implicit datetime conversion?
  28. How to convert text column to datetime in SQL
  29. How to calculate difference in hours (decimal) between two dates in SQL Server?
  30. SQL Server 2008 Spatial: find a point in polygon

Leave a Comment