|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Setting column defaultHi
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 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 -- Show quoteHope 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 > > 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 Show quote "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 >> >> > > 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? -- Show quoteHope 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 >>> >>> >> >> > > 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 Show quote "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 >>>> >>>> >>> >>> >> >> > > 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. -- Show quoteHope 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 >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > BTW, I forgot to mention that the default value will also be inserted when
the keyword default is specified as the inserted value. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "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 >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > John wrote:
> Well I just opened the table in management studio and typed a record into Don't use the GUI to view or manipulate date (this is not Excel!), there > 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? > are various gotchas involved, stuff like you're seeing now, formatting discrepancies, etc... Always use T-SQL scripts for your data manipulation, then you know what's going on. This will override the default constraint: INSERT INTO MyTable (Field1, Field2, DateField) VALUES ('A', 1, NULL) This will not: INSERT INTO MyTable (Field1, Field2) VALUES ('A', 1)  |
|||||||||||||||||||||||
Other interesting topics