| Groups | Blog | Home |
sql server new users : Setting column default
In Transact-SQL, you can add a default constraint with ALTER TABLE like the example below. I would expect the GUI tool's 'Default Value' is the same thing but I don't know for sure because I don't tool you are using. ALTER TABLE dbo.MyTable ADD CONSTRAINT DF_MyDateColumn DEFAULT GETDATE() FOR MyDateColumn -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... > Hi > > How can I make a datetime to default to current date/time? Can I set the > 'Default Value or Binding' property to something to achieve that? > > Thanks > > Regards > >
Default constraint values apply only when the column is not included in the
insert statement. Conversely, if the INSERT statement explicitly specifies NULL, a NULL is inserted instead of the default value. Is this your situation? -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message news:uWbtbifyGHA.4044@TK2MSFTNGP04.phx.gbl... > Tried that but when I manually add a record to the table the column > remains null i.e. it does not default to current date/time. > > Regards > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:uF1cobfyGHA.2516@TK2MSFTNGP06.phx.gbl... >> >> In Transact-SQL, you can add a default constraint with ALTER TABLE like >> the example below. I would expect the GUI tool's 'Default Value' is the >> same thing but I don't know for sure because I don't tool you are using. >> >> ALTER TABLE dbo.MyTable >> ADD CONSTRAINT DF_MyDateColumn >> DEFAULT GETDATE() FOR MyDateColumn >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "John" <John@nospam.infovis.co.uk> wrote in message >> news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... >>> Hi >>> >>> How can I make a datetime to default to current date/time? Can I set the >>> 'Default Value or Binding' property to something to achieve that? >>> >>> Thanks >>> >>> Regards >>> >>> >> >> > >
SSMS is probably generating an INSERT statement with all columns and an
explicit NULL value. You can verify this with a Profiler Trace. If you want the column value to always be GETDATE(), you can override the value specified on the INSERT statement with an UPDATE in an insert trigger. This will effectively ignore the inserted value. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "John" <John@nospam.infovis.co.uk> wrote in message news:eUEfSIgyGHA.2036@TK2MSFTNGP05.phx.gbl... > Well I just opened the table in management studio and typed a record into > the grid provided. I am assuming the management studio is using an insert > with all columns to handle this so date/time did not come up. Any way to > default the column to date/time regardless of what method of insertion is > used, even if it means that field becomes read only? > > Thanks > > Regards > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:uZq5uwfyGHA.4976@TK2MSFTNGP04.phx.gbl... >> Default constraint values apply only when the column is not included in >> the insert statement. Conversely, if the INSERT statement explicitly >> specifies NULL, a NULL is inserted instead of the default value. Is this >> your situation? >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "John" <John@nospam.infovis.co.uk> wrote in message >> news:uWbtbifyGHA.4044@TK2MSFTNGP04.phx.gbl... >>> Tried that but when I manually add a record to the table the column >>> remains null i.e. it does not default to current date/time. >>> >>> Regards >>> >>> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >>> news:uF1cobfyGHA.2516@TK2MSFTNGP06.phx.gbl... >>>> >>>> In Transact-SQL, you can add a default constraint with ALTER TABLE like >>>> the example below. I would expect the GUI tool's 'Default Value' is >>>> the same thing but I don't know for sure because I don't tool you are >>>> using. >>>> >>>> ALTER TABLE dbo.MyTable >>>> ADD CONSTRAINT DF_MyDateColumn >>>> DEFAULT GETDATE() FOR MyDateColumn >>>> >>>> -- >>>> Hope this helps. >>>> >>>> Dan Guzman >>>> SQL Server MVP >>>> >>>> "John" <John@nospam.infovis.co.uk> wrote in message >>>> news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... >>>>> Hi >>>>> >>>>> How can I make a datetime to default to current date/time? Can I set >>>>> the 'Default Value or Binding' property to something to achieve that? >>>>> >>>>> Thanks >>>>> >>>>> Regards >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
BTW, I forgot to mention that the default value will also be inserted when
the keyword default is specified as the inserted value. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:ummdvQgyGHA.2392@TK2MSFTNGP03.phx.gbl... > SSMS is probably generating an INSERT statement with all columns and an > explicit NULL value. You can verify this with a Profiler Trace. > > If you want the column value to always be GETDATE(), you can override the > value specified on the INSERT statement with an UPDATE in an insert > trigger. This will effectively ignore the inserted value. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "John" <John@nospam.infovis.co.uk> wrote in message > news:eUEfSIgyGHA.2036@TK2MSFTNGP05.phx.gbl... >> Well I just opened the table in management studio and typed a record into >> the grid provided. I am assuming the management studio is using an insert >> with all columns to handle this so date/time did not come up. Any way to >> default the column to date/time regardless of what method of insertion is >> used, even if it means that field becomes read only? >> >> Thanks >> >> Regards >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:uZq5uwfyGHA.4976@TK2MSFTNGP04.phx.gbl... >>> Default constraint values apply only when the column is not included in >>> the insert statement. Conversely, if the INSERT statement explicitly >>> specifies NULL, a NULL is inserted instead of the default value. Is >>> this your situation? >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "John" <John@nospam.infovis.co.uk> wrote in message >>> news:uWbtbifyGHA.4044@TK2MSFTNGP04.phx.gbl... >>>> Tried that but when I manually add a record to the table the column >>>> remains null i.e. it does not default to current date/time. >>>> >>>> Regards >>>> >>>> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >>>> news:uF1cobfyGHA.2516@TK2MSFTNGP06.phx.gbl... >>>>> >>>>> In Transact-SQL, you can add a default constraint with ALTER TABLE >>>>> like the example below. I would expect the GUI tool's 'Default Value' >>>>> is the same thing but I don't know for sure because I don't tool you >>>>> are using. >>>>> >>>>> ALTER TABLE dbo.MyTable >>>>> ADD CONSTRAINT DF_MyDateColumn >>>>> DEFAULT GETDATE() FOR MyDateColumn >>>>> >>>>> -- >>>>> Hope this helps. >>>>> >>>>> Dan Guzman >>>>> SQL Server MVP >>>>> >>>>> "John" <John@nospam.infovis.co.uk> wrote in message >>>>> news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... >>>>>> Hi >>>>>> >>>>>> How can I make a datetime to default to current date/time? Can I set >>>>>> the 'Default Value or Binding' property to something to achieve that? >>>>>> >>>>>> Thanks >>>>>> >>>>>> Regards >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Hi
How can I make a datetime to default to current date/time? Can I set the 'Default Value or Binding' property to something to achieve that? Thanks Regards
Tried that but when I manually add a record to the table the column remains
null i.e. it does not default to current date/time. Regards [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:uF1cobfyGHA.2516@TK2MSFTNGP06.phx.gbl... > > In Transact-SQL, you can add a default constraint with ALTER TABLE like > the example below. I would expect the GUI tool's 'Default Value' is the > same thing but I don't know for sure because I don't tool you are using. > > ALTER TABLE dbo.MyTable > ADD CONSTRAINT DF_MyDateColumn > DEFAULT GETDATE() FOR MyDateColumn > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "John" <John@nospam.infovis.co.uk> wrote in message > news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... >> Hi >> >> How can I make a datetime to default to current date/time? Can I set the >> 'Default Value or Binding' property to something to achieve that? >> >> Thanks >> >> Regards >> >> > >
Well I just opened the table in management studio and typed a record into
the grid provided. I am assuming the management studio is using an insert with all columns to handle this so date/time did not come up. Any way to default the column to date/time regardless of what method of insertion is used, even if it means that field becomes read only? Thanks Regards [quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:uZq5uwfyGHA.4976@TK2MSFTNGP04.phx.gbl... > Default constraint values apply only when the column is not included in > the insert statement. Conversely, if the INSERT statement explicitly > specifies NULL, a NULL is inserted instead of the default value. Is this > your situation? > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "John" <John@nospam.infovis.co.uk> wrote in message > news:uWbtbifyGHA.4044@TK2MSFTNGP04.phx.gbl... >> Tried that but when I manually add a record to the table the column >> remains null i.e. it does not default to current date/time. >> >> Regards >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:uF1cobfyGHA.2516@TK2MSFTNGP06.phx.gbl... >>> >>> In Transact-SQL, you can add a default constraint with ALTER TABLE like >>> the example below. I would expect the GUI tool's 'Default Value' is the >>> same thing but I don't know for sure because I don't tool you are using. >>> >>> ALTER TABLE dbo.MyTable >>> ADD CONSTRAINT DF_MyDateColumn >>> DEFAULT GETDATE() FOR MyDateColumn >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "John" <John@nospam.infovis.co.uk> wrote in message >>> news:ee9HLQfyGHA.4104@TK2MSFTNGP02.phx.gbl... >>>> Hi >>>> >>>> How can I make a datetime to default to current date/time? Can I set >>>> the 'Default Value or Binding' property to something to achieve that? >>>> >>>> Thanks >>>> >>>> Regards >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |