SQL Server default date time stamp?

Question

I have a field that when something is inserted I want it to get the current Date & Time and insert this into the database. Is there a way to get the date & time, and set it as the default value?

Currently the default value is: (getdate()) Which sets only the date. How do I also set the time?

What data type is your column? `getdate()` does return both date and time but if you are using data type date you will only store date or if you are using a varchar() that is not long enough. — Mikael Eriksson, Aug 09 '11 at 20:30

score 26 · Accepted Answer · answered Aug 09 '11 at 20:27

26

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.

answered Aug 09 '11 at 20:27

JNK

63,321
15
122
138

What if you don't want to use the current time? – Amicable Aug 15 '13 at 09:48
The question is about returning the current date and time. What do you want exactly/ – JNK Aug 15 '13 at 10:16

score 4 · Answer 2 · answered Aug 09 '11 at 20:45

One easy way is to give the field a default constraint:

create table YourTable 
    (
    ... other columns ...
    CreateDt datetime default getdate(),
    ... other columns ...
    )

A disadvantage of this method is that you can overwrite the value by specifying it in an insert clause.

score 3 · Answer 3 · answered Mar 24 '14 at 08:36

3

Personally I would like to use GETUTCDATE() instead GETDATE() to avoid confusions.

answered Mar 24 '14 at 08:36

Dasun

3,244
1
29
40

2

I would recommend that either. It's very hard to get rid of localtime if you use it. – Jens Marchewka Jul 31 '14 at 14:57

score 2 · Answer 4 · answered Aug 09 '11 at 20:27

2

SYSDATETIME() will get the current date and time.

Make sure the data type of the column is datetime, and not just date or it won't be able to hold a datetime.

answered Aug 09 '11 at 20:27

Ariel

25,995
5
59
69

score 0 · Answer 5 · answered Aug 09 '11 at 20:31

0

Most SQL implementations (engines) do have CURRENT_TIMESTAMP function.

answered Aug 09 '11 at 20:31

ain

22,394
3
54
74

SQL Server default date time stamp?

5 Answers5

Linked