SQL Server Default Date Time Stamp

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

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.

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

How to default the time for the date with SQL Server

This works for SQL Server (SQLFiddle):

SELECT DATEADD(hour, 10, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

Explanation: GETDATE() gives current date and time. Casting to DATE makes it date only (midnight time). Casting it again to DATETIME makes it compatible with DATEADD, which finally adds 10 hours to it.

Which settings provide SQL Server default datetime format?

Don't do that! Don't use strings instead of dates and don't use a culture-specific date or datetime format, it's a recipe for disaster.

SQL Server doesn't have a "date format", just formats it uses to convert dates to and from strings. One of them is the default, controlled by the server's collation. You can neither assume nor should you depend on a specific collation, so you should avoid conversions whenever possible. Moreover, passing a string value can prevent SQL Server from using indexes on date columns because it would have to convert the string to the underlying column type.

It's far easier and safer to pass dates as date-typed variables or parameters. You avoid the entire conversion mess this way, to and from and avoid SQL injection attacks.

There's no reason to pass strings instead of dates to the server. All SQL Server clients (including ADO.NET) allow you to execute parameterized queries. ORMs like NHibernate and Entity Framework also generate parameterized queries for date-typed columns.

Whenever you need to create a string literal, use one of the invariant formats like 20140913 or 2012-11-07T18:26:20

You can read more about it at Writing International T-SQL Statements

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)

What is the default date style used by an instance of SQL Server when converting from varchar to datetime?

It uses a style based on the language. Basically, for the date above, if you're American then the date will be read as yyyy-MM-dd hh:mm:ss, however, if use over languages, then it's be (stupidly) read as yyyy-dd-MM hh:mm:ss.

If you are using strings for dates (like your literal here) then aim to use an unambiguous format. In SQL Server, regardless of data type and language, those are yyyy-MM-ddThh:mm:ss.nnnnnnn and yyyyMMdd.

If you're convert to an (n)varchar, always use a style code (and a length for your varchar) for consistent results.

So, for your value, you can run the below to find out what the default conversion value would be for all the languages on your instance:

DECLARE Languages CURSOR FOR
SELECT alias
FROM sys.syslanguages;

DECLARE @Alias sysname,
        @SQL nvarchar(MAX);

CREATE TABLE #ConvertedDates (Alias sysname, dt datetime, converted nvarchar(25));

DECLARE @dt datetime = '2020-05-06T00:00:00'

OPEN Languages

FETCH NEXT FROM Languages
INTO @Alias;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'SET LANGUAGE ' + QUOTENAME(@Alias) + N'; INSERT INTO #ConvertedDates(Alias,dt,converted) VALUES(N' + QUOTENAME(@Alias,'''') + ',@dt,CONVERT(nvarchar(25),@dt));';

    EXEC sys.sp_executesql @SQL, N'@dt datetime', @dt;

    FETCH NEXT FROM Languages
    INTO @Alias;

END;

CLOSE Languages;
DEALLOCATE Languages;

SELECT *
FROM #ConvertedDates;

DROP TABLE #ConvertedDates;

Yes, that is a Cursor. I wanted to ensure that each dynamic statement ran by itself, to ensure language was preserved for each conversion.

Date / Timestamp to record when a record was added to the table?

You can create a non-nullable DATETIME column on your table, and create a DEFAULT constraint on it to auto populate when a row is added.

e.g.

CREATE TABLE Example
(
SomeField INTEGER,
DateCreated DATETIME NOT NULL DEFAULT(GETDATE())
)

How do you set a default value for a MySQL Datetime column?

IMPORTANT EDIT:
It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

---

Related Topics