Date Format to String

Question

Date Format to String

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
April 11, 2012 at 8:56 am

#256240

Hello Everyone
I know this is simple to most, but I cannot seem to get this correct. I am having issues with the minutes and seconds.
I need a string format of the data to look like this:
YYYYMMDDhhmmss
I have used the DATEPART function to get most of it, but I am not doing something correctly, and it is not coming out correct.
Can anyone assist please.
Thank You in advance for all your help.
Andrew SQLDBA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

AndrewSQLDBA (4/11/2012)
Hello Everyone
I know this is simple to most, but I cannot seem to get this correct. I am having issues with the minutes and seconds.
I need a string format of the data to look like this:
YYYYMMDDhhmmss
I have used the DATEPART function to get most of it, but I am not doing something correctly, and it is not coming out correct.
Can anyone assist please.
Thank You in advance for all your help.
Andrew SQLDBA

You should do this sort of thing in the presentation layer rather than the database layer.

That being said, you could do it like this: -

DECLARE @Date DATETIME = GETDATE();

SELECT @Date, -- Datetime format e.g. 2012-04-11 16:01:32.987

REPLACE(REPLACE(REPLACE( --Your funky format (yyyymmddhhmiss)

CONVERT(VARCHAR(19),@Date,120) --Ensures 24h odbc canonical format e.g. yyyy-mm-dd hh:mi:ss

,':','') --Gets rid of the ":" between the time units

,'-','') --Gets rid of the "-" between the date units

,' ','') --Gets rid of the space between the date and the time

;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

SELECT CONVERT(VARCHAR,@date,112) + '' + REPLACE(CONVERT(VARCHAR,@date,108),':','')

i keep a handy proc sp_dates in my master database so i can get a list of common datetime formats for myself:

i just run sp_dates in SSMS to get my cheat sheet 😀

CREATE PROCEDURE [dbo].[sp_dates](@date as DATETIME=NULL)

AS

BEGIN

IF @date IS NULL

SET @date = getdate()

SELECT CONVERT(VARCHAR,@date,101) AS FormattedDate,'101' AS Code,'SELECT CONVERT(VARCHAR,@date,101)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,102) AS FormattedDate,'102' AS Code,'SELECT CONVERT(VARCHAR,@date,102)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,103) AS FormattedDate,'103' AS Code,'SELECT CONVERT(VARCHAR,@date,103)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,104) AS FormattedDate,'104' AS Code,'SELECT CONVERT(VARCHAR,@date,104)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,105) AS FormattedDate,'105' AS Code,'SELECT CONVERT(VARCHAR,@date,105)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,106) AS FormattedDate,'106' AS Code,'SELECT CONVERT(VARCHAR,@date,106)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,107) AS FormattedDate,'107' AS Code,'SELECT CONVERT(VARCHAR,@date,107)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,108) AS FormattedDate,'108' AS Code,'SELECT CONVERT(VARCHAR,@date,108)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,109) AS FormattedDate,'109' AS Code,'SELECT CONVERT(VARCHAR,@date,109)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,110) AS FormattedDate,'110' AS Code,'SELECT CONVERT(VARCHAR,@date,110)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,111) AS FormattedDate,'111' AS Code,'SELECT CONVERT(VARCHAR,@date,111)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,112) AS FormattedDate,'112' AS Code,'SELECT CONVERT(VARCHAR,@date,112)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,113) AS FormattedDate,'113' AS Code,'SELECT CONVERT(VARCHAR,@date,113)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,114) AS FormattedDate,'114' AS Code,'SELECT CONVERT(VARCHAR,@date,114)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,120) AS FormattedDate,'120' AS Code,'SELECT CONVERT(VARCHAR,@date,120)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,121) AS FormattedDate,'121' AS Code,'SELECT CONVERT(VARCHAR,@date,121)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,126) AS FormattedDate,'126' AS Code,'SELECT CONVERT(VARCHAR,@date,126)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,130) AS FormattedDate,'130' AS Code,'SELECT CONVERT(VARCHAR,@date,130)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,131) AS FormattedDate,'131' AS Code,'SELECT CONVERT(VARCHAR,@date,131)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,112) + '-' + CONVERT(VARCHAR,@date,114) AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + CONVERT(VARCHAR,@date,114)' AS SQL UNION

SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,108),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR,@date,108),'':'','''')' UNION

SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,114),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR,@date,114),'':'','''')' AS SQL

ORDER BY CODE

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

This is what I came up with:

select replace(replace(replace(convert(varchar(30), getdate(), 120),'-',''),':',''),' ','')

drew.allen SSC Guru Points: 76996 More actions · Answer 4

Using datapart, you'll lose the leading zeros. Convert it to the format closest to what you want and then clean it up using REPLACE.

SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar(25), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Nice Lowell 😀

I did a quick test to compare, as I normally do when I see something different.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SET NOCOUNT ON;

--1,000,000 rows of dates

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);

DECLARE @HOLDER VARCHAR(20);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== BASELINE ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER = COUNT(*)

FROM #testEnvironment;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== NESTED REPLACE ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER =

REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),randomDate,120),':',''),'-',''),' ','')

FROM #testEnvironment;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);

PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';

PRINT REPLICATE('-',80);

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

PRINT '========== DATE + VARCHAR ==========';

SET STATISTICS TIME ON;

SELECT @HOLDER =

CONVERT(VARCHAR,randomDate,112) + '' + REPLACE(CONVERT(VARCHAR,randomDate,108),':','')

FROM #testEnvironment;

SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 325 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== NESTED REPLACE ==========

SQL Server Execution Times:

CPU time = 8843 ms, elapsed time = 8903 ms.

================================================================================

--------------------------------------------------------------------------------

CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE

--------------------------------------------------------------------------------

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

========== DATE + VARCHAR ==========

SQL Server Execution Times:

CPU time = 2765 ms, elapsed time = 2994 ms.

================================================================================

Your method is generally about 3* faster than the nested replace on the small test box I ran this on.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 6

Cadavre (4/11/2012)
Nice Lowell 😀
I did a quick test to compare, as I normally do when I see something different.

Cadavre I always love your performance tests.

I have no idea how many you've posted that i copied to my snippets, but it's a lot!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

Thanks Everyone

I always learn something everyday on here. I too love the performance tests.

Andrew SQLDBA

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Once again Lowell shares another great time saving utility. Thanks!! Now if I can just remember that I have that. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/