Removing millisecond from datetime throwing error

Question

I have this code in SQL Server 2016

SELECT CONVERT (DATETIME, CONVERT(varchar(8), ExpiryDate))

I get this result:

ExpiryDate ------------------------ 2020-08-03 00:00:00.000

How can I remove the .000 (the milliseconds part)?

Expected result should be:

2020-08-03 00:00:00

Please help

Why do you want to show HH:MI:SS if it's a DATE?

dnoeth
– dnoeth

2020-09-23 12:02:30 +00:00
Commented Sep 23, 2020 at 12:02 — dnoeth
– dnoeth, Commented Sep 23, 2020 at 12:02

Thom A · Accepted Answer · 2020-09-23 11:54:25Z

2

By not using a datetime, which is accurate to 1/300th of a second. Instead define your value as a datetime2(0), which is accurate to 1 second (due to having a precision of 0 on milliseconds):

SELECT CONVERT(datetime2(0),ExpiryDate) FROM ...

answered Sep 23, 2020 at 11:54

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

3 Comments

Thom A Over a year ago

@GordonLinoff dbfiddle always shows dates and time data types in the format yyyy-MM-dd hh:mm:ss.nnn

Gordon Linoff Over a year ago

It seemed strange that it didn't work. I'll delete the comment.

Thom A Over a year ago

To confirm, use an IDE, and the milliseconds are missing. Example

Gordon Linoff · Accepted Answer · 2020-09-23 11:59:09Z

You are confusing the internal format and the display format. If you want the value formatted in a particular way, then format it explicitly:

select CONVERT(VARCHAR(19), expirydate, 121)

You can add this into the table as a computed column:

alter table t add expirydate_display as (CONVERT(VARCHAR(19), expirydate, 121))

Collectives™ on Stack Overflow

Removing millisecond from datetime throwing error

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related