Update sql date field in mssqlserver with YYYY-MM-DD format

Question

Hi I would like to ask you how to write a statement that will update the date field in database; I have a table that contains one row and one of the valaues is in

YYYY-MM-DD HH:MM:SS.MMM

Where MMM is a part of second. How to write it?

Answer 1

For the current date

update your_table
set date_field = getdate()

or for the current date and time

update your_table
set date_field = current_timestamp

or for any datetime

update your_table
set date_field = '2013-09-23 12:00:00.000'

Answer 2

You're mixing two things. One is formatting a datetime type when displaying/casting to varchar and the other is the type itself.

The type itself stores datetime internally and for the sake of this question it doesn't concern you. It's always the same. How you see it - in i.e. SQL Server Management Studio - is another matter completely and is dependent on casting to string directly in query ( cast(dt_column as varchar(20))) or indirectly by the tool you're using.

If you want to manually set a datetime type, you ussually write a string with the date that is directly casted or indirectly casted to datetime format i.e. declare @dt datetime = '2012-01-01 23:00:00' is indirect while declare @dt datetime = cast('2012-01-01 23:00:00' as datetime) is direct.

The format YYYY-MM-DD HH24:MI:SS.mmm is rather canonical so I'm pretty sure that you can just use indirect casting from string so you can just write:

update [table_name_here]
set [column_name_here] = '2013-01-01 23:12:11.123'
where ... --this line only if needed; if you have only one row you may skip it

Of course replace my date in ' ' with your own.