How to convert timestamp to datetime

Question

How to convert datetime to timestamp ?

Timestamp like 'yyyy-mm-dd hh:mm:ss.mss' I try to format this

format(time_field , 'dd.mm.yyyy hh:mm:ss')

but in the end it turns out '1.43.2019 01:43:03'

Excuse me, but have you tried writing it ... just the same as you want it ? — Pac0, Feb 05 '19 at 10:29
Please Google before posting duplicates: https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format — JGFMK, Feb 05 '19 at 10:29
relevant : [Common mistakes in datetime formatting and parsing](https://codeblog.jonskeet.uk/2015/05/05/common-mistakes-in-datetime-formatting-and-parsing/) by Jon Skeet — Pac0, Feb 05 '19 at 10:30
time_field is a misleading _column_ name. Tables have _columns_, not fields. A timestamp has minute, second etc _fields_. — jarlh, Feb 05 '19 at 10:30

S.Jose · Answer 1 · 2019-02-05T12:47:09.903

2

use MM for month when formatting

SELECT FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss')

edited Feb 05 '19 at 12:47

answered Feb 05 '19 at 10:37

S.Jose

1

May I add to that. If you want 24-hour format you should use `HH` instead of `hh` or append the string with `tt` for AM/PM. – NotFound Feb 05 '19 at 10:51

score -1 · Answer 2 · answered Feb 05 '19 at 10:30

-1

use the built in convert function, with a suitable option.

 SELECT CONVERT(varchar, getdate(), 21);

answered Feb 05 '19 at 10:30

Cato

1

any explanation from the person who decided to thumb this down? Would be appreciated. – Cato Feb 05 '19 at 10:40

score -1 · Answer 3 · answered Feb 05 '19 at 11:52

You don't actually need to use format() for this, because the format you want is a "built-in" format for SQL Server's convert() function:

select convert(varchar(255), timefield, 120)

I'm not a big fan on convert() and its arcane formatting options. However, options 120 and 121 are pretty much the ones that I know and use.

3 Answers3