Converting Date Format SQL to YYYY-MM-DD HH:MM:SS

Question

I currently have the date format 2016-11-23 16:47:21.007 and I am trying to convert it with this SQL:

update user_table set login_date = CONVERT(datetime,login_date,120)

So that I only get 2016-11-23 16:47:21 but it's currently just staying as the same format.

Am I missing something?

It seems your `login_date` column is already a `datetime`, so there is no need to reformat it since it has no intrinsic format. You just need formatting when you are going to output it not when storing it. — Wagner DosAnjos, Nov 23 '16 at 16:53
I want this format `2016-11-23 16:47:21.007` to be converted to this format `2016-11-23 16:47:21` Just basically without the milliseconds — J.Do, Nov 23 '16 at 16:54
@wdosanjos ... and it doesn't make much sense to update a datetime column with a varchar. I'm surprised it even runs without error, let alone that it doesn't change the column. — Tim Biegeleisen, Nov 23 '16 at 16:54
login_date is datetime, I wanted to converted just the general format/layout of the datetime — J.Do, Nov 23 '16 at 16:55
if it's just for displaying you can use `select CONVERT(varchar(19),'2016-11-23 16:47:21.007',120)` but as everyone suggests, converting it is pretty silly since you need to convert it back to use it as a datetime. I suggest you handle this on the application side. — S3S, Nov 23 '16 at 16:56
@TimBiegeleisen MSSQL will implicitly parse the `varchar` value to a `datetime`. That's why it doesn't fail. — Wagner DosAnjos, Nov 23 '16 at 16:57
@wdosanjos So the whole operation ends up basically being a no-op. Thanks, I just learned something new. — Tim Biegeleisen, Nov 23 '16 at 16:58

Tim Biegeleisen · Answer 1 · 2016-11-23T17:25:19.160

5

Your current requirement is mainly a presentation need. Since your login_date is already stored as a datetime, there is no need to change anything internally in your database.

When you need to present the login_date without milliseconds, then go ahead and call CONVERT as you were:

SELECT CONVERT(varchar(19), login_date, 120) AS login_date_no_millis
FROM yourTable

But there is no need to do the UPDATE you showed us in your question.

edited Nov 23 '16 at 17:25

answered Nov 23 '16 at 16:56

Tim Biegeleisen

502,043
27
286
360

1

don't you need a varchar not a datetime for the conversion? – S3S Nov 23 '16 at 16:58
3

You should specify a length for your `varchar` - [not only because it is a good idea to always do so in general](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx), but also because in this case you can use the length to omit the milliseconds, which is what the OP asking for. No real impact here but you can probably also use `char` because the output will always be the same length. – Aaron Bertrand Nov 23 '16 at 17:23
It's also worth mentioning that [Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Nov 23 '16 at 17:47

score 0 · Answer 2 · answered Nov 23 '16 at 19:52

Altering the column to datatype datetime2(0) will mean milliseconds aren't stored. If you're certain you don't need them.

Equally, casting it to datetime2(0) in your select will do the same in the presentation level whilst maintaining the milliseconds in the database if required. Also means that unlike cast/convert to varchar, it's still a datetime value rather than a string if that makes a difference to the app.

Converting Date Format SQL to YYYY-MM-DD HH:MM:SS

2 Answers2