Updating sql date field in mssqlserver using YYYY-MM-DD format

Question

Hi, I would like to ask you how to write an instruction that will update the date field in the database; I have a table containing one row and one of the icons is in

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

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

Answer 1

For current date

 update your_table set date_field = getdate() 

or for 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 mix two things. One of them formats the datetime type when displaying / casting in varchar, and the other - the type itself.

The type itself stores the date and time inside, and for this question it does not concern you. It is always the same. How you see it - for example, in SQL Server Management Studio - is a completely different matter and depends on whether the row is directly in the query ( cast(dt_column as varchar(20)) ) or indirectly by the tool you use.

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

The format of YYYY-MM-DD HH24: MI: SS.mmm is pretty canonical, so I'm sure you can just use indirect casting from a string so you can simply 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 yours.