Add Default Value of Datetime Field in SQL Server to a Timestamp

Add default value of datetime field in SQL Server to a timestamp

For modifying an existing column in an existing table:

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

Default Value for DATETIME column in SQL Server Management Studio 2017

Define a constraint using TSQL:

ALTER TABLE TableName ADD CONSTRAINT DF_TableName DEFAULT GETDATE() FOR ColumnName

Set NOW() as Default Value for datetime datatype?

As of MySQL 5.6.5, you can use the DATETIME type with a dynamic default value:

CREATE TABLE foo (
    creation_time      DATETIME DEFAULT   CURRENT_TIMESTAMP,
    modification_time  DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Or even combine both rules:

modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Reference:

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Prior to 5.6.5, you need to use the TIMESTAMP data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP field can exist per table.

CREATE TABLE mytable (
  mydate TIMESTAMP
)

See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

If you want to prevent MySQL from updating the timestamp value on UPDATE (so that it only triggers on INSERT) you can change the definition to:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

SQL Server default date time stamp?

GETDATE() is a date and time in SQL Server.

Run SELECT GETDATE() to verify this.

What is the datatype of your field? If it's DATE then it will not hold time values as well.

Create a datetime column in SQL Server 2008 with default value as current timestamp UTC

For times with timezone information, use DATETIMEOFFSET in SQL Server (2008 and newer):

create table dbo.Dummy
(
   id int,
   status int,
   node_id varchar(512),
   createdDTTM DateTimeOffset NOT NULL default SYSDATETIMEOFFSET()
);

Using the SYSDATETIMEOFFSET() you're getting the default current date and time as DATETIMEOFFSET (in the local timezone your server is located in) from SQL Server.

Or maybe you're looking for SYSUTCDATETIME() instead which gives you the current date and time in UTC format? This works just fine with DATETIME2(n) or DATETIMEOFFSET columns (in SQL Server 2008 and newer, I'd recommend not to use DATETIME anymore)

Set the default value of a datetime column to be in 24h format in SQL Server

Thank you so much everyone ! So to sum up I learned from you that "Datetime" is a binary value so it has no format.

So if it displays good in the SQL Server and not in my PHP website it is because php interpret the binary value differently.

To finally make it displays the way I want in PHP I format it in PHP using (note : $row["Date"] is the cell where I get the "Date" binary value from the SQL Server) :

$datee = new DateTime($row["Date"]);
$dateee = $datee->format('Y/m/d H:i:s'); 

And the output is now :

2016/06/22 10:46:00

---

Related Topics