CODEWITHSUNDEEP
sql-server-2008t-sqldatetime
Refracted Paladin
Refracted Paladin

Reputation: 12216

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'

Upvotes: 1

Views: 30172

Answers (5)

boeing777
boeing777

Reputation: 33

In BigQuery, this will work

FORMAT_DATETIME("%G%m%d%H%M%S",Datetime)

Datetime example: 2022-01-02T23:59:00

Output:

YYYYMMDDHHMMSS (20220101235900)

Upvotes: 0

Markus Jarderot
Markus Jarderot

Reputation: 89171

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.

Upvotes: 3

Fauzi88
Fauzi88

Reputation: 713

Try this for format YYYYMMDDHHMISS

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

Upvotes: 1

MarkD
MarkD

Reputation: 5316

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), ':', '')

Upvotes: 7

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

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.

Upvotes: 9

Related Questions