Convert data and time into a numeric number in sql server

Question

I have a data & time format column which I would like to convert to the following format in Microsoft SQL Server: yyyymmddhhmmss00000

So for example if I have 2021-02-04 11:49:50 this will be converted to 2021020411495000000.

Any one knows how to do it please?

Have a look at CONVERT, REPLACE and the concatenation operator (+). — Thom A
– Thom A ♦, Commented Feb 4, 2021 at 10:54

Charlieface · Accepted Answer · 2021-02-12 09:10:22Z

2

You can use the FORMAT function:

SELECT FORMAT(myDate, 'yyyyMMddHHmmss00000')

edited Feb 12, 2021 at 9:10

answered Feb 4, 2021 at 11:59

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Annalise Azzopardi Over a year ago

thank you for this but you have to be careful when using this because it doesn't distinguish between AM and PM

Charlieface Over a year ago

Sorry needs to be uppercase HH have corrected

Tyron78 · Accepted Answer · 2021-02-04 11:07:36Z

1

By converting the date to NVARCHAR once with format 112 and once with format 8 you can extract the numeric date and the time without milliseconds. After removing : from the time you can concat these two strings and convert them to bigint. Following an example:

DECLARE @d DATETIME = GETDATE()
SELECT CAST(CONVERT(NVARCHAR(8), @d, 112) + REPLACE(CONVERT(NVARCHAR(8), @d, 8), ':', '') + '00000' AS BIGINT)

answered Feb 4, 2021 at 11:07

Tyron78

4,1973 gold badges21 silver badges32 bronze badges

2 Comments

Thom A Over a year ago

Does it "need" to be an nvarchar? As the value only consists of numerical characters, why not use a varchar (which is half the size), or even better (as it's fixed width) a char?

Tyron78 Over a year ago

@Larnu thanks for the hint. Yes, you are right - char and varchar work as well. Sorry I didn't think of this - we just used the nvarchar way in several of our queries and I just rememberd it

Collectives™ on Stack Overflow

Convert data and time into a numeric number in sql server

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related