SQL Server Performance Forum – Threads Archive

How to store system date?

Hi,
i am one table "sub1" having fields name,property & subdate.In this subdate i want to insert system date, which should be done automatically. for that i ve tried in default , i ve written date().But it is giving me error.any one tell me how to do this. whenever i will add new record,automatically system date will be stored in that subdate field.

You use an application to insert the record to SQL?

i am inserting all other records through asp(frontend).i want other fields i will insert from frontend & subdate field will automatically take system date by default. i don’t want to insert through query, datatype of that field is datetime. can it is possible. same thing possible in MSAccess,if i give in defult as date(),then it takes system date as default value.pls tell me the solution.

to get current date & time use getdate() not date()
set the default as getdate() and you have to omit the column during the insertion. ——
> KH <

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> set the default as getdate() and you have to omit the column during the insertion. <hr height="1" noshade id="quote"></blockquote id="quote"> Slight correction here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You certainly *can* pass a value to a column with a default on it, but you don’t have to. If you omit it, SQL Server will insert the default. If you pass a value, SQL Server will insert that value and ignore the default. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Note that if you insert any value into a column, even a NULL, then the default is not applied. If you have a table Tbl with two columns Col1 and Col2, which both have a default of 0, then … INSERT INTO Tbl (Col1, Col2) VALUES (NULL, NULL)
… will insert a row with two NULLs INSERT INTO Tbl (Col1) VALUES (NULL)
… will insert a row with a NULL on Col1, and a 0 on Col2 INSERT INTO Tbl (Col2) VALUES (NULL)
… will insert a row with a 0 on Col1, and a NULL on Col2 And it’s the same if you use a SELECT statement, instead of a VALUES list.

In that case <pre id="code"> INSERT INTO Tbl (Col1, Col2) VALUES (NULL, NULL) </pre id="code"> where all columns have DEFAULTs, you can use the nice shortcut <pre id="code"> INSERT INTO Tbl (Col1, Col2) DEFAULT VALUES </pre id="code"> Saves some keystrokes.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

In case you’re inserting data into other columns, then don’t mention the columns where you want to get the defaults. Saves another couple of keystrokes.[8D]

The INTO is also optional. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Get someone else to type it in for you.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>