How to get YYYYMMDDHHMMSS

Question

Post reply

How to get YYYYMMDDHHMMSS

Bruin

SSCrazy

Points: 2058
More actions
October 21, 2021 at 2:27 pm
Go to Answer
#3942866

This is getting the data I need, but not the format.
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')
10212021102437
I want YYYYMMDD before the time portion.
Thanks.
homebrew01

SSC Guru

Points: 55548
More actions
October 21, 2021 at 2:36 pm
Answer
#3942881

102 instead of 101 ?
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles

Viewing 15 posts - 1 through 15 (of 15 total)

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

Bruin SSCrazy Points: 2058 More actions · Answer 1

Bruin

SSCrazy

Points: 2058

October 21, 2021 at 3:14 pm

#3942892

Thanks!!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

You can cut out one of the REPLACEs. Look at format # 112.

--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 Moden SSC Guru Points: 1004329 More actions · Answer 3

p.s. Using VARCHAR with no length is one of those code smells that you might want to avoid.

--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)

planetmatt SSCrazy Points: 2207 More actions · Answer 4

Replace all that with

SELECT FORMAT(GETDATE(),'yyyyMMddhhmmss')

GaryV Hall of Fame Points: 3307 More actions · Answer 5

You could use 120 and then replace the dashes, colons and space with ''. Is 3 REPLACE's worse than calling GETDATE twice?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

planetmatt wrote:

Replace all that with
SELECT FORMAT(GETDATE(),'yyyyMMddhhmmss')

You need to be really careful there. FORMAT is about 43 times slower than just about anything you might want to do with CONVERT. It's been proven many times.

--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)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

Jeff Moden wrote:

p.s. Using VARCHAR with no length is one of those code smells that you might want to avoid.

It would default to varchar(30) , but still to wide for a normal iso datetime.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

This takes 3 replaces - but gets it done without concatenation:

Select convertedtime = replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', '')

The problem with all of these is the return value from replace is varchar(8000). If you need a specific data type then you need to add an additional cast/convert around the result.

I also don't like using varchar when we know the length of the input won't change. For all dates/times - getdate() with a format of 120 will always return 19 characters, and that tells us that the final data type should be char(19) also:

Select convertedtime = cast(replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', '') as char(19))
Select convertedtime = convert(char(19), replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', ''))

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

MMartin1 wrote:

Jeff Moden wrote:
p.s. Using VARCHAR with no length is one of those code smells that you might want to avoid.
It would default to varchar(30) , but still to wide for a normal iso datetime.

Yep. I know about the default. In other cases (and I forget what they are but don't worry about them because I NEVER rely on the defaults), they default to 1.

You do hint at the reason in your note... data type disparity, which also leads to things like very expensive datatype mismatches in JOIN and WHERE criteria. It frequently also means that someone was too lazy to do their homework to "right-size" their datatypes.

I do 100% reviews on code in our shop and using any variable-width datatype with no indication of size will always be sent back to the developer for proper sizing. As you say, a "30" would not be correct here and would also fail review.

--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 Moden SSC Guru Points: 1004329 More actions · Answer 10

A lot of the methods posed so far do the job but are actually relatively slow because of all the string manipulation using both concatenation and REPLACE. Using a nice general binary collation cuts about 30% of the time out but that's still relatively slow.

A pure math and a hybrid CONVERT with a couple of STUFFs results in a 50-55% improvement (twice as fast).

CONVERT, by itself, is nasty fast. As a bit of a sidebar, anything and everything is at least 7 times faster than (ugh!) FORMAT (FORMAT took 73 seconds on a 10 million row table). Don't use FORMAT for anything in SQL Server even for small stuff. It will become a part of the "Death by a Thousand Cuts" performance issues that most servers suffer.

If you really want performance to go along with accuracy and still be relatively simple, try the following. The added benefit is that the result can be stored in an 8 bit BIGINT instead of a 14 byte CHAR(14). For a 10 million row table, the other code posted took 10.4 seconds to run. The code below only takes about 3.6 seconds or about 3 times faster.

 SELECT YYYYMMDDHHMISS = CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000
                       + DATEPART(hh,GETDATE())*10000
                       + DATEPART(mi,GETDATE())*100
                       + DATEPART(ss,GETDATE())
;

If you really need the output to be character based and you're dumping it to a table, the the implicit conversion to a CHAR(14) column will only slow the code above down to about 3.9 seconds. If you want the nice warm fuzzies that avoiding implicit conversions provides, the following will do the trick with no addition time added.

 SELECT YYYYMMDDHHMISS = CONVERT(CHAR(14),
                                 CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000
                                +DATEPART(hh,GETDATE())*10000
                                +DATEPART(mi,GETDATE())*100
                                +DATEPART(ss,GETDATE())
                         )
;

Yeah... I know what a lot of folks are thinking. "Big deal! 3.6 seconds v.s. 10.4 on 10 MILLION rows! WHO CARES!!!????).

The answer is, everyone should... all the time. Just imagine if ALL your code ran almost 3 times faster with almost 3 times less CPU than the stuff you have running now. Heh... and if your stuff is in the cloud, thing of the money you might save not only on processors but on SQL Server licenses, as well.

"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty!"

--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 Moden SSC Guru Points: 1004329 More actions · Answer 11

Bruin wrote:

This is getting the data I need, but not the format.
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')
10212021102437
I want YYYYMMDD before the time portion.
Thanks.

Just out of sheer curiosity, why do you need to do this?

--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)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 12

Data type disparity on join conditions etc is not really an after thought I've seen over time, its more like a 'no thought.' You bring up an excellent point that a simple adjustment here should be done to pass code review.

And just a fun fact regarding char/varchar, from Microsoft docs, > "When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30."

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

Thank you for the great feedback and the quote from BOL.

Now if we could get the OP to cough up the reason for wanting to do this task. 😀

--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)