Convert DateTime to yyyyMMddHHmm in T-SQL

问题:

Is something like the below my only option? I was hoping there was a better, cleaner, way to accomplish this.

This being, my dates need to end up formatted like so --> 201301080105 for 1:05 AM on 1/8/13.

SELECT SUBSTRING(REPLACE(CONVERT(varchar, GETDATE(), 111), '/','') + REPLACE(CONVERT(varchar, GETDATE(), 108), ':',''),0,13) AS 'My Formatted Date'

回答1:

This has a few fewer pieces of chopping than the original, but I'd stand by my comment that it's better to do this in your presentation layer - or anywhere, really, with general formatting facilities, rather than the DB:

select CONVERT(varchar(20),myDate,112) + REPLACE(CONVERT(varchar(5),myDate,108),':','')

Observations: 112 is a better style to use for converting the date portion, since it already has no separators. And, if you specify a length for a conversion and the converted string won't fit, then the rest gets discarded. That's how I eliminated the seconds portion from the time, by only allowing space for hours and minutes. But there's no style that doesn't include separators in the time portion.

回答2:

This is YYYYMMDDHHMMSS

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':','')

For YYYYMMDDHHMM

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 108), ':',''),4)

OR

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '')

回答3:

Try this for format YYYYMMDDHHMISS

SELECT replace(replace(replace(CONVERT(VARCHAR(19), GETDATE(), 120),'-',''),' ',''),':','')

回答4:

In SQL Server 2012+ and Azure SQL Database you can use

SELECT FORMAT(GETDATE(), 'yyyyMMddHHmmss')

Though this does not work with Azure SQL Data Warehouse.