setting GETDATE() to only give out day on default value in MSSQL

Question

I'm trying to set a column with a default value or binding of the current date in MSSQL.
I'm currently using GETDATE(), but this gives me the timestamp with the hours, minutes, seconds and milliseconds, I only need the day part (2015-03-05).

Only results I found on the web were these of changing it in the SELECT statement.

cast(getdate() as date), You can't change the setting to get a different result for getdate() — t-clausen.dk, Mar 05 '15 at 10:10
The field itself should be defined as type Date, not any other data type that stores the time element. `cast(getdate() as date)` is great, but also see http://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server for alternatives — Paul Maxwell, Mar 05 '15 at 10:43
Did you try using the same suggestions you found for SELECT statements in the DEFAULT statement? — Tab Alleman, Mar 05 '15 at 14:50
Yes, of course, I get an error stating that the input is not valid. — Nir Tzezana, Mar 05 '15 at 15:06

Staeff · Accepted Answer · 2015-03-05T10:17:02.037

2

If you use it in a date context SQL Server will auto cast it for you

DECLARE @date DATE = GETDATE();

SELECT  @date

-- result: 2015-03-05

or you could simply use a cast

SELECT CAST(GETDATE() as DATE)

EDIT:

I'm still not sure if I get what you want, but if you want to do it as a default constraints it works the same way:

create table #table
(
    id int,
    insertDate date default GETDATE()
)

insert into #table (id) values (1)

select top 1 insertDate from #table

-- result: 2015-03-05

edited Mar 05 '15 at 10:17

answered Mar 05 '15 at 10:11

Staeff

4,994
6
34
58

I'd like it to happen when I insert a value, on the deafult binding, if that's possible, not on SELECT. – Nir Tzezana Mar 05 '15 at 10:13
if the column of your insert is a date type it will also auto cast it for you – Staeff Mar 05 '15 at 10:13
1

I've edited my post, because I guess you want to have this as a default constraint right? – Staeff Mar 05 '15 at 10:18

score 0 · Answer 2 · answered Mar 05 '15 at 10:16

If you want to store only date, excluding time you can use this:

CREATE TABLE #dts(id INT IDENTITY, d_date datetime2 DEFAULT CONVERT(char(10), GETDATE(), 126))
INSERT #dts DEFAULT VALUES 
SELECT * FROM #dts

However it will return you zeroes instead of time, as seen here:

id  d_date
-------------------------------
1   2015-03-05 00:00:00.0000000

You can remove unwanted characters using LEFT function:

SELECT id, LEFT(d_date, 10) FROM #dts

It will return you:

id  d_date
--------------
1   2015-03-05

You could achieve this by using varchar as datatype, but i dont think it would be appropiate solution and it's better to format date in select statement. But if you really need it, then this works:

CREATE TABLE #dts(id INT IDENTITY, d_date VARCHAR(10) DEFAULT CONVERT(char(10), GETDATE(), 126))
INSERT #dts DEFAULT VALUES 
SELECT * FROM #dts

Output:

id  d_date
--------------
1   2015-03-05

Maybe because I'm using different environment settings. Try this: `SELECT id, LEFT(CONVERT(char(10), d_date, 126)), 10) FROM #dts` — Evaldas Buinauskas, Mar 05 '15 at 12:09

score 0 · Answer 3 · answered Mar 05 '15 at 10:17

0

Set your default value on the column to

(CONVERT([date],getdate(),0))

I have used it many times

answered Mar 05 '15 at 10:17

Fred

5,663
4
45
74

Tanner · Answer 4 · 2015-03-05T10:25:35.227

0

The answer to your problem is simple. Change the format of the column that is going to hold the value to a DATE (as opposed to a data type that will hold the time portion, i.e: DATETIME).

Then set the default to GETDATE() and because the destination column is a DATE the time portion will be stripped off for you.

Take this sample code:

CREATE TABLE #temp (id INT, CreatedDate DATE DEFAULT GETDATE())

INSERT INTO #temp ( id)
VALUES ( 1 ),( 2 ),( 3 )

SELECT * FROM #temp

DROP TABLE #temp

Output:

id  CreatedDate
1   2015-03-05
2   2015-03-05
3   2015-03-05

edited Mar 05 '15 at 10:25

answered Mar 05 '15 at 10:19

Tanner

22,205
9
65
83

I have no "date" datatype, only "datetime" – Nir Tzezana Mar 05 '15 at 10:35
@NirTzezana what version of sql server are you using? if you're not interested in the time portion, change it's data type to `date` if it's available. – Tanner Mar 05 '15 at 10:42

score 0 · Answer 5 · answered Mar 05 '15 at 10:23

In other ways to convert DATETIME to DATE (get only date without time) you can use CAST to DATE format.

SELECT CAST(GETDATE() AS DATE)

In your case you can set default value type DATE without CASTing.

CREATE TABLE #TempTable
(
   Id INT,
   ..... ,
   CurrentDate DATE DEFAULT GETDATE()
)

score 0 · Answer 6 · answered Mar 05 '15 at 10:26

You can use CONVERT() to get date in different formats. From http://www.w3schools.com/sql/func_convert.asp :

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

gives you the following results:

Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243

setting GETDATE() to only give out day on default value in MSSQL

6 Answers6