insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode],, [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

INSERT/UPDATE Date Format problem...

Hi,
This is a problem that everybody knows I guess: When you INSERT our UPDATE a
date in an Sql Server half of the time your date changes. For example: you
want to input two dates: 13th of May (13/05/2003) and 12th of May
(12/05/2003). The first one will always be in the database as "13/05/2003"
because the database knows 13 can't hbe a month. But for the second one you
need to get lucky: there's always a big chance (depending on the regional
settings?) that he will put it in the database as "05/12/2003" and thinks it
is 5th of December instead of 12th of May.
I used to have this problem in VB6, and now again I have it in VB.NET. In
VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
dd/MM/yyyy.
But still I think this isn't a 'nice' way. There should be a way which is
independed of regional settigns etc, and doens't force you to use 'trics'.
Does anybody here know how to do this?
Thanks a lot in advance!
PieterAny of the following formats are "safe" - they work independently of the
server's regional settings
'20031231'
'2003-12-31T17:59:00'
'2003-12-31T17:59:00.000'
Example:
UPDATE Sometable
SET datecol = '20031231'
WHERE ...
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks! I will try this!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Ws-dnW0JbYJ2fUiiRVn-jg@.giganews.com...
> Any of the following formats are "safe" - they work independently of the
> server's regional settings
> '20031231'
> '2003-12-31T17:59:00'
> '2003-12-31T17:59:00.000'
> Example:
> UPDATE Sometable
> SET datecol = '20031231'
> WHERE ...
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||<%
'This function recieves a date from text string in format dd/mm/yy or
dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a
datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a
CCYY year
'--
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'--
'pass a date as dd/mm/yy
Function convDate(theDate)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2)) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:3fd5df92$0$289$ba620e4c@.reader5.news.skynet.be...
> Hi,
> This is a problem that everybody knows I guess: When you INSERT our UPDATE
a
> date in an Sql Server half of the time your date changes. For example: you
> want to input two dates: 13th of May (13/05/2003) and 12th of May
> (12/05/2003). The first one will always be in the database as "13/05/2003"
> because the database knows 13 can't hbe a month. But for the second one
you
> need to get lucky: there's always a big chance (depending on the regional
> settings?) that he will put it in the database as "05/12/2003" and thinks
it
> is 5th of December instead of 12th of May.
> I used to have this problem in VB6, and now again I have it in VB.NET. In
> VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
> dd/MM/yyyy.
> But still I think this isn't a 'nice' way. There should be a way which is
> independed of regional settigns etc, and doens't force you to use 'trics'.
> Does anybody here know how to do this?
> Thanks a lot in advance!
> Pieter
>

Insert trigger changing record

Hello all!
I want to create an insert trigger to change some fields of the inserted
record. I want to put in two fields the system date and system time.
When I try to update Inserte table I get an error telling me I cannot
update inserted tables.
Can anyone give me a hand on this?
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
update Inserted
set CreationDate = dbo.idlog_date(current_timestamp)
--
Function idlog_date returns the date in my format.
Thanks in advance,
Hugo MadureiraHugo Madureira wrote:
> Hello all!
> I want to create an insert trigger to change some fields of the inserted
> record. I want to put in two fields the system date and system time.
> When I try to update Inserte table I get an error telling me I cannot
> update inserted tables.
> Can anyone give me a hand on this?
>
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
UPDATE dbo.transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
GO
It seems like overkill to use a trigger for this. Have you considered
declaring a DEFAULT value instead: DEFAULT CURRENT_TIMESTAMP.

> Function idlog_date returns the date in my format.
A DATETIME column doesn't have a "format". Why store the date as
anything other than DATETIME or SMALLDATETIME?
David Portas
SQL Server MVP
--|||Yes, thats right. You have to update the original data which is already
store in there.
UPDATE Transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
FROM Transactions T
INNER JOIN INSERTED I
ON T.<YourprimaryKey> = I.<YourprimaryKey>
HTH, Jens Suessmeyer

Insert trigger

Hi

I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
Turnover, VAT, Netturnover). I get a file which I have to import every
know and then, with new data. In this file I only get values for (ID,
Date, Turnover and VAT). The import is working fine with the import
wizard.

The problem is, that I want to have the Netturnover computed at the
time of insert to equal [Turnover-VAT], but I don't really know how to
as I'm new to these triggers.

Could anyone help me I would appriciate this.
BR / Janjazpar (jannoergaard@.hotmail.com) writes:
> I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> Turnover, VAT, Netturnover). I get a file which I have to import every
> know and then, with new data. In this file I only get values for (ID,
> Date, Turnover and VAT). The import is working fine with the import
> wizard.
> The problem is, that I want to have the Netturnover computed at the
> time of insert to equal [Turnover-VAT], but I don't really know how to
> as I'm new to these triggers.

The simplest is to make NetTurnover a computed column:

CREATE TABLE DebtorTurnover
(ID int NOT NULL,
Date datetime NOT NULL,
Turnover decimal(10,2) NOT NULL,
VAT decimal(10, 2) NOT NULL,
Netturnover AS Turnover - VAT)

A trigger would look like this:

CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
FOR INSERT, UPDATE AS
UPDATE DebtorTurnover
SET Netturnover = dt.Turnover - dt.VAT
FROM DebtorTurnover dt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.ID = i.ID

The "inserted" table is a virtual table that holds the inserted rows,
or in case of an UPDATE, the update rows after the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:

> jazpar (jannoergaard@.hotmail.com) writes:
> > I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> > Turnover, VAT, Netturnover). I get a file which I have to import every
> > know and then, with new data. In this file I only get values for (ID,
> > Date, Turnover and VAT). The import is working fine with the import
> > wizard.
> > The problem is, that I want to have the Netturnover computed at the
> > time of insert to equal [Turnover-VAT], but I don't really know how to
> > as I'm new to these triggers.
> The simplest is to make NetTurnover a computed column:
> CREATE TABLE DebtorTurnover
> (ID int NOT NULL,
> Date datetime NOT NULL,
> Turnover decimal(10,2) NOT NULL,
> VAT decimal(10, 2) NOT NULL,
> Netturnover AS Turnover - VAT)
> A trigger would look like this:
> CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
> FOR INSERT, UPDATE AS
> UPDATE DebtorTurnover
> SET Netturnover = dt.Turnover - dt.VAT
> FROM DebtorTurnover dt
> WHERE EXISTS (SELECT *
> FROM inserted dt
> WHERE dt.ID = i.ID
> The "inserted" table is a virtual table that holds the inserted rows,
> or in case of an UPDATE, the update rows after the table.
Hi Thanks for you reply

I made the following

Table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DepTurnOver]
GO

CREATE TABLE [dbo].[DepTurnOver] (
[Year] [int] NULL ,
[Week] [int] NULL ,
[CalleId] [int] NULL ,
[ShopId] [int] NULL ,
[ItemGroupId] [int] NULL ,
[TurnOver] [real] NULL ,
[Discount] [real] NULL ,
[Qty] [real] NULL ,
[Customer] [int] NULL ,
[VAT] [real] NULL ,
[Consumption] [real] NULL,
[Netturnover] AS [Turnover]-[VAT]
) ON [PRIMARY]
GO

Trigger:
CREATE TRIGGER DepTurnover_tri ON DepTurnover
FOR INSERT, UPDATE AS
UPDATE DepTurnover
SET Netturnover = idt.Turnover - idt.VAT
FROM DepTurnover idt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.Year = idt.Year
AND dt.Week = idt.week
AND dt.CalleId = idt.CalleId
AND dt.ShopId = idt.ShopId
AND dt.ItemGroupId = idt.ItemGroupId)

But when I try to save the trigger I get the following error:
Server: Msg 271, Level 16, State 1, Procedure DepTurnover_tri, Line 3
Column 'Netturnover' cannot be modified because it is a computed
column.

Have I done anything wrong here.

Thanks in advance
BR/ Jan

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||jazpar (jannoergaard@.hotmail.com) writes:
> I made the following
> Table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[DepTurnOver]
> GO
> CREATE TABLE [dbo].[DepTurnOver] (
> [Year] [int] NULL ,
> [Week] [int] NULL ,
> [CalleId] [int] NULL ,
> [ShopId] [int] NULL ,
> [ItemGroupId] [int] NULL ,
> [TurnOver] [real] NULL ,
> [Discount] [real] NULL ,
> [Qty] [real] NULL ,
> [Customer] [int] NULL ,
> [VAT] [real] NULL ,
> [Consumption] [real] NULL,
> [Netturnover] AS [Turnover]-[VAT]
> ) ON [PRIMARY]
> GO
> Trigger:

Sorry, I was a bit brief. If you have a computed column, you don't
need the trigger at all. I included the trigger code, in case you
were not in position to change the table definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

sql

Insert statement with todays date in one of the field

How do I write an Insert SQL statement with a default today's date inserted into one of the field?

Help is apreciated.

INSERT INTO [TableName]

(

DateField

)

VALUES

(

getdate()

)

|||

sorry didn't see the word default

you would set a paramter of type date and set the default value to getdate()

@.DateField as DateTime = getdate()

If you pass a paramter it will override this default value

|||

Thank you so much for the immediate response. Actually it was an update not an insert but it is similar. Here's what I've tried.

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# DateTime.Now%> WHERE [dirID] = @.dirID">

I am using SqlDataSource for this. The error I got from the above statement is:

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.

So I tried this:

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# getDate()%> WHERE [dirID] = @.dirID">

And then in the getDate method, I do this:

protected string getDate() {string strDate = Convert.ToString(DateTime.Now);return strDate; }

But I still get the same error.|||You don't need the <%# %> tags. getdate() is a function inside sql, not c#|||Thank you so much! That works great.|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

Undefined function 'getDate' in expression

|||

You need to seperate the getdate function from the string

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, " + getdate() + ")"

|||

Sorry for the earlier double post. I needed Date() function as it is an access database.

INSERT statement for datetime .

In Oracle , v can use select and insert for a date column as below:

for select :
--------------------
select A,to_char(CDATE,'DD-MM-YY:HH:MI:SS AM') from db.table1;

for insert:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into db.table1
(A,CDATE)
values
( 2, TO_Date('20/01/2005 11:08:33 PM', 'DD/MM/YYYY HH:MI:SS AM'));
--------------------

But How v can use datetime in MSSQL Server ?
What are the corresponding select and insert statement in this ?

Any one have idea in this ?

thankshave you checked the manual? because it's in there

SQL Server recognizes date and time data enclosed in single quotation marks (') in these formats:
Alphabetic date formats (for example, 'April 15, 1998')
Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
Unseparated string formats (for example, '19981207', 'December 12, 1998')|||what about time ? Can I insert a date with a time ?
Please help with an example .|||have you tried anything yet?

do you think this will work --

insert into db.table1 (A,CDATE) values ( 2, '20/01/2005 11:08:33 PM' )

or do you think this will work --

insert into db.table1 (A,CDATE) values ( 2, '2005-01-20 23:08:33' )

what do you think?|||oh yes ... it is working....

insert into db.dbo.table1 (A,CDATE) values ( 2, '20-jan-2005 11:08:33 PM' )

Thanks r937.

INSERT smalldatetime problem

Hallo,

I am trying to insert date in a table in my database, where column type is smalldatetime. Query works fine if date format ismm.dd.yy:

INSERT INTO DateTable (DateValue) VALUES ('8.18.2007 22:00:00') works fine!

But if the time format is dd.mm.yy it does not work:

INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') does not work!

The error message is:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Is there any chanceto execute(dd.mm.yy)INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') properly?

Thanx!
Marko

just do like this.....wat ever it may be the input type......

INSERT INTO DateTable (DateValue) VALUES datetime.parse('18.8.2007 22:00:00').tostring("yyyy-MM-dd");

Ramesh

|||

Actually, my query looks something like this:

INSERT INTO Table1(ID, Name, Date) VALUES(100, 'Tom', '18.8.2007')

So, I am not sure where to put datetime.parse and tostring(... Tried to type query(datetime.parse and tostring()...) in SQL Server Management Studio, but I got lot of error messages...

|||

I solved my probleme here:http://forums.asp.net/p/1148508/1866912.aspx

Thanx anyway!
Marko

|||

This would be a better approach (Done in VB.NET, but you should be able to convert it to C# easily). Area's marked with ... are missing, and are irrelevant:

dim conn as new sqlconnection(...)

dim cmd as new sqlcommand("INSERT INTO MyTable(col1) VALUES (@.val1)",conn)

cmd.Parameters.Add("@.val1",sqldbtype.datetime).Value=calCalendar1.selecteddate

conn.open

cmd.executenonquery

conn.close

The above shows how we can supply a datetime to T-SQL as a true datetime instead of first converting a datetime to a string (Which may be affected by either the webserver's current culture or the clients current culture), and then trying to parse the resulting string on SQL Server using it's current culture. By avoiding the datetime->string->datetime conversion process, culture becomes irrelevant.

|||

I will keep this in my record, and will use it for testing my application after it is finished!

Thanx!
Marko

Insert querry

Hello

Can anyone give me the code to insert date from textbox to database(SQL Server 2000). The date fromat in SQL is dd-mm-yyyy.

Rathish

rathish,

here is some code if you wish to take the code behind approach.

string yourDate;yourDate = txtBox.Text;string insertStr ="insert into yourTable values ('" + yourDate +"')";SqlConnection conn =new SqlConnection(connectionStr);SqlCommand cmd =new SqlCommand(insertStr, conn);using(conn){using(cmd){ conn.Open(); cmd.ExecuteNonQuery(); }}you can try something like that. hope it helps! -- jp

|||

Hello

Its giving me the following error:

Syntax error convertig datetime from character string.

The datatype is datetime in the database.

Rathish

|||

Hi,

I would suggest you use parameters update database

string yourDate;
yourDate = txtBox.Text;
string insertStr = "insert into yourTable(datefield) values (@.datefield)";
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(insertStr, conn);
cmd.Parameters.Add("@.datefield", SqlDbType.DateTime, 8)
cmd.Parameters["@.datefield"].Value = DateTime.Parse(yourDate)
using(conn){
using(cmd){
conn.Open();
cmd.ExecuteNonQuery();
}
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.sqlmonster.com
Mike via SQLMonster.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com
|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.sqlmonster.com

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.droptable.comMike via droptable.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.droptable.com