0

I am trying to format a datetime2 into yyyy-mm-dd hh:mm:ss format.

I have tried the below options:

This is throwing an error:

SELECT CONVERT(datetime2, 120);

This works fine, but I need the output of type datetime2

SELECT CONVERT(VARCHAR(19), GETDATE(), 120);

How can I achieve this?

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Leo
  • 5,017
  • 6
  • 32
  • 55
  • 3
    Well, **either** you have a variable / value of type `DATETIME2` (which has **no formatting** - it's a **binary** value) - or then you convert it into a **formatted string** - you can't have both at the same time ..... – marc_s Jun 10 '19 at 08:18
  • 1
    _this is throwing an error: `SELECT CONVERT(datetime2, 120);`_ Sure it will because you don't specify you will convert it to what, cause you need to call `CONVERT()` as `SELECT CONVERT(VARCHAR(19), DateTime2ColOrVariable, 120)` – Ilyes Jun 10 '19 at 08:22
  • If you want it to have a "format" and be a `datetime2`, then yo need to handle the formatting in the **presentation layer**. As @marc_s said, Date and time datatypes, in SQL Server, don't have formats, they are stored as a binary value. – Thom A Jun 10 '19 at 08:27
  • [DateTime2 has no *display* format](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028). Only string representations of it has a display format. – Zohar Peled Jun 10 '19 at 08:53

1 Answers1

3

You are calling the CONVERT() function without specifying the data type to convert to, so in your case SELECT CONVERT(datetime2, 120); SQL Server will try to convert the value 120 to a datetime2 and that's why you get this error (which you don't provide)

Explicit conversion from data type int to datetime2 is not allowed.

To use CONVERT() with date and time styles, you need to pass

CONVERT(<datatype to convert to>, <Value to be converted>, <Style>).

The SYSUTCDATETIME returns a datetime2 you can convert to as

SELECT CONVERT(VARCHAR(20), SYSUTCDATETIME(), 120)
--Change SYSUTCDATETIME() with your column/variable

For what you say

but I need the output of type datetime2

A DATETIME doesn't have a format, it's a binary value, thus you need to convert it to a formatted string. Since you need to return a DATETIME2 then you need to leave it as it is, and do the formatting in presentation layer.

Ilyes
  • 14,640
  • 4
  • 29
  • 55