Change the date format
-
Hi All,
How could I change a datetime field to the following format?
YYYYMMDDHHMMSS
Thanks in advance
-
You wouldn't actually change the format of the column... just what the results of a SELECT on the column look like... (using GETDATE() in place of a column here)...
SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(26),GETDATE(),120),'-',''),' ',''),':','')
HOWEVER!... Unless your requirement is to output such a thing to a file from SQL Server, the formatting of dates should be done in the GUI to take advantage of any local settings present.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff
-
You bet, Adam. Thanks for posting back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, you could save yourself a couple of "REPLACE"'s
select convert(varchar(10), getdate(), 112) + replace(convert(varchar(8), getdate(), 108), ':', '')
--------------------
Colt 45 - the original point and click interface
-
Heh... could save all of it if they were to format it in the GUI where it belongs
Wonder which would actually be faster? 3 replaces and a convert or 2 converts, a replace, and a concatenation?
Oooooh... I know.... let's convert it to a BIG INT... c'mon, it'll be fun
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You know me... can't resist a good test
I'm a bit surprised at the apparent slothfulness of the Replace method compared to the Convert method, but wait 'till you see the Numeric method Didn't know that butt ugly could be so fast 
--DROP TABLE #JBMTest GO --===== Create and populate a 1,000,000 row test table. -- Column RowNum has a range of 1 to 1,000,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Takes about 42 seconds to execute. SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT), SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)), SomeCSV = 'Part1,Part2,Part3,Part4,Part5,Part6,Part7,Part8,Part9,Part10', SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY), SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) INTO #JBMTest FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned ALTER TABLE #JBMTest ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Do a duration test for both DECLARE @StartTime DATETIME DECLARE @BitBucket CHAR(14)
--===== Test the REPLACE method SET @StartTime = GETDATE() SELECT @BitBucket = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(26),SomeDate,120),'-',''),' ',''),':','') FROM #JBMTest PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Replace method'
--===== Test the CONVERT method SET @StartTime = GETDATE() SELECT @BitBucket = convert(varchar(10), SomeDate, 112) + replace(convert(varchar(8), SomeDate, 108), ':', '') FROM #JBMTest PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Convert method'
--===== Surprise, surprise... the NUMERIC method SET @StartTime = GETDATE() SELECT @BitBucket = YEAR(SomeDate) *10000000000 + MONTH(SomeDate) *100000000 + DAY(SomeDate) *1000000 + DATEPART(hh,SomeDate)*10000 + DATEPART(mi,SomeDate)*100 + DATEPART(hh,SomeDate) FROM #JBMTest PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Numeric method'
00:00:10:967 Replace method 00:00:08:140 Convert method 00:00:07:347 Numeric method
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My 2 pence for Adam.
Datetime field does not have any format.
So, there is nothing to change.
In fact datetime is FLOAT value reflecting number of days (including fraction) passed since the beginning of year 1900.
Whatever string representation of datetime you see is just a result of conversion performed by front-end application.
Different applications do it differently. That's why same datetime value will appear in different formats in EM and QA.
And nothing should stop you from setting up your own datetime representation in you own front-end application according to the requirements you've got.
_____________
Code for TallyGenerator -
I use the following code:
SELECT CONVERT(char(8),GETDATE(),112) + CONVERT(char(2), GETDATE(),114) + SUBSTRING(CONVERT(char(5), GETDATE(),114),4,2)
-
Hey, Phil...
I just went back and read my reply about testing it... it sounds a lot sarcastic and I didn't mean to make it sound that way. Hope you took no offense... my sarcasm was directed at formatting dates in SQL if a GUI is available.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff
No offense taken as I never thought there was offense meant
--------------------
Colt 45 - the original point and click interface -
Jeff - I don't understand why you would NOT expect an arithmetic process to be faster than a string process. i would expect that all the date functions are intrinsically arithmetic - fractionating a float value (or its integer portion).
-
Not surprised... just thought REPLACE would be closer.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply