1

I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy.

Thank you.

  • What is the data type of the column? If it is a datetime, then you will want to return the data in that format, not store it in a specific format. Also what database are you using? – Taryn Dec 9 '14 at 15:59
  • i can add another column for it, i'm using SQL 2008 – user1203397 Dec 9 '14 at 16:08
  • Please add the schema of the table to your question. – Thomas Kejser Dec 9 '14 at 16:11
  • just 2 column name and datetime – user1203397 Dec 9 '14 at 16:19
  • i can use date format yyyy-mm-dd – user1203397 Dec 9 '14 at 16:21
1

Try converting the datetime column as follows:

UPDATE [TableName]
    SET DateCreate = CAST(LEFT(CONVERT(NVARCHAR(25), DateCreate, 120), 10) AS DATETIME)
  • it will reset time to 00:00:00 but not remove it and date format still yyy-mm-dd. can we remove the time and change date format to mm-dd-yyyy – user1203397 Dec 9 '14 at 16:46
1

For SQl Server 2008 you can use convert function like below.

--For output format mm-dd-yyyy
declare @d datetime = getdate() 
select convert(varchar(10), @d, 110)

--For output format yyyy-mm-dd
declare @d datetime = getdate() 
select convert(varchar(10), @d, 120)

If you are using SQL Server 2012 and later, the new format function works fine too. Example below.

--For output format mm-dd-yyyy    
declare @d datetime = getdate()
select format(@d,'MM-dd-yyyy', 'en-US')

--For output format yyyy-mm-dd
declare @d datetime = getdate() 
select format(@d,'yyyy-MM-dd', 'en-US')
  • i have several table and not sure how to use this function to apply the right table – user1203397 Dec 9 '14 at 16:41
  • You can use the function in your select statement with each table where you have a datetime column you want to convert. Example: select convert(varchar(10),mydatecolumn, 110) from mytable; For update use update statement like, update mytable set mydatecolumn = cast(convert(varchar(10),mydatecolumn, 110) as datetime); – user353gre3 Dec 9 '14 at 16:44
  • If you do not want the time component in your data table, you need to create a new column with date datatype and update data into that column. – user353gre3 Dec 9 '14 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.