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 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 Fails

All,

Trying to format some data before I drop it into a grid. I have this in a stored proc but it fails

CREATE TABLE dbo.tmpSummary (
AE NVARCHAR(50)
, PRODUCT_LINE NVARCHAR(20)
, ANNUAL_REV NUMERIC (9)
, [GRWTH/ACQ] NUMERIC (9)
, RETENTION NUMERIC (9)
, CATEGORY NVARCHAR(20)

)

INSERT INTO dbo.tmpSummary (
[AE]
, [PRODUCT_LINE]
, [ANNUAL_REV]
, [GRWTH/ACQ]
, RETENTION
, CATEGORY
)
SELECT
A.AE
, A.PRODUCT_LINE
, A.ANNUAL_REV
, A.[GRWTH/ACQ]
, A.RETENTION
, B.PRODUCT_CATEGORY AS CATEGORY

FROM
tmpSummary A RIGHT OUTER JOIN PRODUCT B
On A.PRODUCT_LINE=B.PRODUCT_CATEGORY

I keep getting an error "Invalid Column name CATEGORY" Anyone know why? Thanks

Never mind. Maybe if I learn to read i could see that i am trying to insert data BACK into the same table. It should have been something else.

|||

Only reason why you would get that error is that you don't have a column in your table named CATEGORY. Check your table defenition again to make sure you have the spelling of the column name correct. I know that gets me alot

Insert sp_spaceused output into table

Hello,
I need to get database space space usage information into one table and then
format the output in one report.
Im using the sp_spaceused stored procedure to get this information but
because it sends the output separate in two blocks i cant insert it into one
table.
I send you what im doing but it doesnt function.
create table dbsize
(
database_name varchar(128),
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
insert into dbsize exec sp_spaceused
Can you help me?
Thanks and best regardsYou could try gathering the information you need from the system tables.
-Argenis
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:796CCE66-1418-4072-9DFA-3AD3F63E95DF@.microsoft.com...
> Hello,
> I need to get database space space usage information into one table and
then
> format the output in one report.
> Im using the sp_spaceused stored procedure to get this information but
> because it sends the output separate in two blocks i cant insert it into
one
> table.
> I send you what im doing but it doesnt function.
> create table dbsize
> (
> database_name varchar(128),
> database_size varchar(18),
> [unallocated space] varchar(18),
> reserved varchar(18),
> data varchar(18),
> index_size varchar(18),
> unused varchar(18)
> )
> insert into dbsize exec sp_spaceused
> Can you help me?
> Thanks and best regards

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