-1

I have a column in a SQL Server database of datatype DATETIME.

Currently the value is in this format: 2054-12-31T00:00:00.0000000

I want to convert this column values into this format : 2054-12-31T00:00:00

This conversion of value should happen while I select the column in SELECT query statement at run time

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Srujan K.N.
  • 51
  • 1
  • 9
  • 2
    "Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date – Mitch Wheat Nov 11 '18 at 09:03

1 Answers1

4

DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.

In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

You're probably looking for style #126 - so you can use this in your SELECT query:

SELECT
    CONVERT(VARCHAR(50), YourDateTimeColumn, 126)

and that should do it

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    @SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved. – Zohar Peled Nov 11 '18 at 09:53
  • Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved – Srujan K.N. Nov 11 '18 at 12:08