none
convert date to YYYY-MM-DD

    Question

  •  

    Hi,

     

    How can I update a date format to YYYY-MM-DD

     

    EXAMPLE:

    Jan  2 2008 12:00AM  TO 2008-01-02

     

    THANKS.

    Friday, August 15, 2008 5:25 PM

Answers

All replies

  • If the column is a datetime then it is stored as a datetime and you can not update it

    In the output you can do this

     

    Code Snippet
    select left(convert(varchar(30),getdate(),120),10)

     

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks
    Friday, August 15, 2008 5:34 PM
  •  

     

    YOU'RE AWESOME, THANK YOU.

     

    Friday, August 15, 2008 5:41 PM
  •  

     

    Hi again,

     

    I used this update

     

    UPDATE table a

    SET CONTRACT_DATE=left(convert(varchar(30),CONTRACT_DATE,120),10)

     

    but it returns like this

    Mar 29 200

     

    it was fine on the first time? I don't know what's wrong?

     

     

     SQL_Menace wrote:

    If the column is a datetime then it is stored as a datetime and you can not update it

    In the output you can do this

     

    Code Snippet
    select left(convert(varchar(30),getdate(),120),10)

     

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks
    Friday, August 15, 2008 5:54 PM
  • The code that Denis provided, is only formatting the way that you are seeing the data, not the way that is stored.

     

    So, in essence, your update - removed the time portion of the date setting it to midnight 00:00:00.

     

    When you want to select the data back, you need to use the same conversion:

     

    select left(convert(varchar(30),CONTRACT_DATE,120),10)

     

    to display it in the format you want to see it in

    Friday, August 15, 2008 6:18 PM
  • That is right it is just for display purposes, datetime is stored internally as 2 ints, read How Are Dates Stored In SQL Server?

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

    Friday, August 15, 2008 6:27 PM
  • ... finetuning ..

    like this and you don't need the LEFT() part.

    Code Snippet

    SELECT CONVERT(varchar(10), GETDATE(), 120); 

     

     

    Friday, August 15, 2008 6:43 PM