|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Automaticaly popluating Current Date in a Db FieldHi,
I have a field in he db called LogDate. I have made it a timestamp type. Is there a formula or a default value i can set for this field in the table design view so that everytime a record is added, this field is automaticallly populated with teh current date? Thanks -- pmud First, you need to define the field as a datetime data type. The timestamp
data type is used for concurrency checking using optomistic locking and does not represent an actual date and time. I typically use a default value of CURRENT_TIMESTAMP to put the current date and time in a field. -- Show quoteGeoff N. Hiten Senior Database Administrator Microsoft SQL Server MVP "pmud" <p***@discussions.microsoft.com> wrote in message news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@microsoft.com... > Hi, > > I have a field in he db called LogDate. I have made it a timestamp type. > Is > there a formula or a default value i can set for this field in the table > design view so that everytime a record is added, this field is > automaticallly > populated with teh current date? > > Thanks > -- > pmud Hi Aaron and Geoff,
Thanks for the reply. I made it a datetiem, and in the default value, I used the function, GetDate() , and it worked. :) -- Show quotepmud "Geoff N. Hiten" wrote: > First, you need to define the field as a datetime data type. The timestamp > data type is used for concurrency checking using optomistic locking and does > not represent an actual date and time. > > I typically use a default value of CURRENT_TIMESTAMP to put the current date > and time in a field. > > -- > Geoff N. Hiten > Senior Database Administrator > Microsoft SQL Server MVP > > > > "pmud" <p***@discussions.microsoft.com> wrote in message > news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@microsoft.com... > > Hi, > > > > I have a field in he db called LogDate. I have made it a timestamp type. > > Is > > there a formula or a default value i can set for this field in the table > > design view so that everytime a record is added, this field is > > automaticallly > > populated with teh current date? > > > > Thanks > > -- > > pmud > > > Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
(cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely equivalent for SQL Server, except using getdate() will provoke the "Wrath of CELKO" (tm). :) -- Show quoteGeoff N. Hiten Senior Database Administrator Microsoft SQL Server MVP "pmud" <p***@discussions.microsoft.com> wrote in message news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@microsoft.com... > Hi Aaron and Geoff, > > Thanks for the reply. I made it a datetiem, and in the default value, I > used > the function, GetDate() , and it worked. :) > > > -- > pmud > > > "Geoff N. Hiten" wrote: > >> First, you need to define the field as a datetime data type. The >> timestamp >> data type is used for concurrency checking using optomistic locking and >> does >> not represent an actual date and time. >> >> I typically use a default value of CURRENT_TIMESTAMP to put the current >> date >> and time in a field. >> >> -- >> Geoff N. Hiten >> Senior Database Administrator >> Microsoft SQL Server MVP >> >> >> >> "pmud" <p***@discussions.microsoft.com> wrote in message >> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@microsoft.com... >> > Hi, >> > >> > I have a field in he db called LogDate. I have made it a timestamp >> > type. >> > Is >> > there a formula or a default value i can set for this field in the >> > table >> > design view so that everytime a record is added, this field is >> > automaticallly >> > populated with teh current date? >> > >> > Thanks >> > -- >> > pmud >> >> >> Hi geoff,
I had saved my tiable with GetDate() as the default value adn data type as datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as suggested by you, but when i click on Save , the default value automatically changes to GetDate() . What do you suggest for this? Can this be a problem? Thanks -- Show quotepmud "Geoff N. Hiten" wrote: > Good. Just FYI, getdate() is the T-SQL specific function. The ANSI > (cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely > equivalent for SQL Server, except using getdate() will provoke the "Wrath of > CELKO" (tm). :) > > -- > Geoff N. Hiten > Senior Database Administrator > Microsoft SQL Server MVP > > > > > "pmud" <p***@discussions.microsoft.com> wrote in message > news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@microsoft.com... > > Hi Aaron and Geoff, > > > > Thanks for the reply. I made it a datetiem, and in the default value, I > > used > > the function, GetDate() , and it worked. :) > > > > > > -- > > pmud > > > > > > "Geoff N. Hiten" wrote: > > > >> First, you need to define the field as a datetime data type. The > >> timestamp > >> data type is used for concurrency checking using optomistic locking and > >> does > >> not represent an actual date and time. > >> > >> I typically use a default value of CURRENT_TIMESTAMP to put the current > >> date > >> and time in a field. > >> > >> -- > >> Geoff N. Hiten > >> Senior Database Administrator > >> Microsoft SQL Server MVP > >> > >> > >> > >> "pmud" <p***@discussions.microsoft.com> wrote in message > >> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@microsoft.com... > >> > Hi, > >> > > >> > I have a field in he db called LogDate. I have made it a timestamp > >> > type. > >> > Is > >> > there a formula or a default value i can set for this field in the > >> > table > >> > design view so that everytime a record is added, this field is > >> > automaticallly > >> > populated with teh current date? > >> > > >> > Thanks > >> > -- > >> > pmud > >> > >> > >> > > > > I had saved my tiable with GetDate() as the default value adn data type as This is Enterprise Manager playing tricks on you. My suggestion is to stop > datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as > suggested > by you, but when i click on Save , the default value automatically changes > to > GetDate() . What do you suggest for this? Can this be a problem? using Enterprise Manager for these things, use Query Analyzer and learn the T-SQL equivalent(s). A Ok. Thanks Aaron. I will try through Quey Analyzer.
-- Show quotepmud "Aaron Bertrand [SQL Server MVP]" wrote: > > I had saved my tiable with GetDate() as the default value adn data type as > > datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as > > suggested > > by you, but when i click on Save , the default value automatically changes > > to > > GetDate() . What do you suggest for this? Can this be a problem? > > This is Enterprise Manager playing tricks on you. My suggestion is to stop > using Enterprise Manager for these things, use Query Analyzer and learn the > T-SQL equivalent(s). > > A > > > > Ok. Thanks Aaron. I will try through Quey Analyzer. If you use Enterprise Manager to "verify" be aware that it may still want to convert CURRENT_TIMESTAMP to GETDATE(). Did youy read anything in the SQL Server documentation about the timestamp
datatype? In spite of its poor name, it has nothing to do with date or time. Try the following instead: CREATE TABLE dbo.MyStuff ( StuffName NVARCHAR(32) PRIMARY KEY, CreatedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ); GO INSERT dbo.MyStuff(StuffName) SELECT N'foo'; WAITFOR DELAY '00:00:01'; INSERT dbo.MyStuff(StuffName) SELECT N'bar'; GO SELECT StuffName, CreatedDate FROM dbo.MyStuff; DROP TABLE dbo.MyStuff; Show quote "pmud" <p***@discussions.microsoft.com> wrote in message news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@microsoft.com... > Hi, > > I have a field in he db called LogDate. I have made it a timestamp type. > Is > there a formula or a default value i can set for this field in the table > design view so that everytime a record is added, this field is > automaticallly > populated with teh current date? > > Thanks > -- > pmud  |
|||||||||||||||||||||||
Other interesting topics