Automaticaly popluating Current Date in a Db Field

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 automaticalll
y
populated with teh current date?
Thanks
--
pmudDid 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;
"pmud" <pmud@.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|||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" <pmud@.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. :)
pmud
"Geoff N. Hiten" wrote:

> First, you need to define the field as a datetime data type. The timestam
p
> data type is used for concurrency checking using optomistic locking and do
es
> not represent an actual date and time.
> I typically use a default value of CURRENT_TIMESTAMP to put the current da
te
> and time in a field.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
>
>|||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" <pmud@.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:
>|||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 t
o
GetDate() . What do you suggest for this? Can this be a problem?
Thanks
--
pmud
"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" <pmud@.discussions.microsoft.com> wrote in message
> news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
>
>|||> 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.
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is Enterprise Manager playing tricks on you. My suggestion is to sto
p
> using Enterprise Manager for these things, use Query Analyzer and learn th
e
> 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().

automatically send a notice after 6 months (was "Date Question")

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).

;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:

PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)

Automatically Add date Range - 7 Days

Hello,

I'm writting a Crystal report on all order sent out in the past week.
I all works fine, but I am now trying to take away all the steps I can to make it easier and quicker to run.
The report has to inputs, the ClientCode and the Date Range.
Is there any way/Code that I can use to replace the need to input a date that will get the report to take todays date as the End range ( which is does at the moment ) and the - 7 days and use the result as the Start Range?

Thanks in advanceYou could use the LastFullWeek function in your record selection.

{table.field} = LastFullWeek

GJ

Automatic Date Field

Hi, I am in the process of building a table and would
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John

Automate "Week Ending" Date

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-----------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ? , StartTime) . I have no idea where to go!

--------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
------------------If you insist on performing an UPDATE on the field, then some variation of the statement below should suffice:


...SET WeekEnding =dateadd(day, 8-datepart(dw, StartTime), StartTime)...


I would make WeekEnd a computed column like this:

alter table add Weekend as dateadd(day, 8-datepart(dw, StartTime), StartTime)|||Should be in here somewhere

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx|||thanks a bunch

Autofill Date Parameters do not work when deployed to server

Good morning all,

I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.

In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of ‘RP2’ report parameter. (rsReportParameterProcessingError)'

I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:

for opening date:

=IIf(Month(Now()) = 1, CDate("01/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("01/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("01/02/" & Year(Now())), IIf(Month(NOw()) = 4, CDate("01/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("01/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("01/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("01/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("01/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("01/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("01/09/" & Year(Now())), IIf(Month(Now()) = 11, Cdate("01/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("01/11/" & Year(Now())), CDate("01/12/1900")))))))))))))

for closing date (pretty similar really, this is the parameter with which the report server finds an error):

=IIf(Month(Now()) = 1, CDate("31/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("31/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("28/02/" & Year(Now())), IIf(Month(Now()) = 4, CDate("31/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("30/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("31/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("30/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("31/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("31/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("30/09/" & Year(Now())), IIf(Month(Now()) = 11, CDate("31/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("30/11/" & Year(Now())), CDate("31/12/1900")))))))))))))

The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.

If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.

Humble thanks,

Paul

I think I've found out what is going on, just not sure how to fix it yet.

The problem is unique to me, everyone else can run the report from the report server and the auto date parameters work just fine.

When I built the report I used English United Kingdom as the default language, the report server's language is set as default to English United Kingdom, as is my machine. For some reason when I try to view the report it changes the date format to English US, or some other equivalent mm/dd/yyyy format and as there are only 12 months in a year the end date parameter falls over because it sees the day as being a month. However, no one else in our organisation has this problem and the report runs perfectly with the parameters being filled automatically as they should.

If I find the reason why my system default date format is being bypassed I'll post it on the forum in case anyone else encounters a similar problem.

Paul

|||

Okay, I've been a dumb schmuck, the language in Internet Explorer was set as English US, and as Reporting Services is a Web-based service it was using this information as the default language setting.

Since I've changed it to English UK everything works as it should.

Paul

Auto update a field with the current date/time

How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added?

Thanks ahead for the help,

Jason

Check out BOL to see if the timestamp column can solve your requirements.|||I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?|||

That is correct. You can set the default value for a column to getdate() or getutcdate(). That takes care of the inserts (if the column isn't mentioned in the insert statement).

For the updates, you'll need to write an update trigger.

|||

jasonburrwc84:

I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?

Trigger is covered in the thread below. Hope this helps.

http://forums.asp.net/thread/1071147.aspx

Auto time stamp in Sql. Express

Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.

Thanks in advance

You can set up the default value for the column as getdate() in the design view of the table.

auto insert fro date in database ..how?

how i make date field in database auto updated,some one told me to use '" Now "' in insert command, but it dos't work, i think it's for asp. regardsYou need to use either GetDate() to get the local machine date/time or GetUtcDate() to get the date time in UTC (GMT).|||You can set up a column with a default value of Getdate() and not worry about manually inserting a value into it, or even use the timestamp column.|||hey guy's thanks for information it's working good. now the getdate() for date, what alse i can use like getdate() to import information??
regards|||

Please mark the post as answered and thank you for the appreciation !

Auto increment based on date

I have a Primary Key column that I would like to have formatted as follows: 'yyyymmdd[auto increment]' . I would also like the auto increment to restart at the beginning of a new month. Any idea's on how to achieve this?

Thanks

You will have to manually control all the inserts via stored procs. Have a separate column with identity property, do the insert, get the Id via SCOPE_IDENTITY() then update your column with the concatenated value. Only drawback is you cannot have it as PK since the value has to be calculated. You can however have a unique constraint on the column. Alternatively if you have a separate table that has just one column with identity values, you can do an insert there first, get the seed value and then use that to insert into your regular table with the concatenation.

|||

An interesting concept for sure but doesn't this smack of a normalization violation which may be the least of your problems with this idea. I'd keep the date in a separate column and use a normal auto increment. From that you should be able to accomplish just about everything you're trying to do with this unusual idea including presenting the user with a computed column that looks like 'yyyymmdd[order number].'

If I just had to succeed with this idea I'd consider writing a function that returns your special key but you'd probably have to input your last increment used from an embedded SELECT statement.

Something like INSERT tblName (pKey,...) VALUES( dbo.fnGetPrimary((SELECT MAX(pKey) from tblName WHERE (pKey > (DATEPART(.. hand build the earliest possible key for the current month which could be a separate function.)) ), .....)

If dbo.fnGetPrimary is fed a null then it would know it is the first entry of the current month. If dbo.fnGetPrimary is fed a value such as 2007110510 then you would just increment it by 1 and return it.

I'd be a little concerned with performance and concurrency with this idea - two records trying to insert and both trying to use the same primary key. You might be able to avoid that with a transaction.

Any way it was fun playing around with it. Good Luck.