Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Services > Date Format Problem - SQL Server Insert From Web Application

Reply
Thread Tools

Date Format Problem - SQL Server Insert From Web Application

 
 
Steve
Guest
Posts: n/a
 
      05-01-2005
Hi,

I've written a short aspx file so that end users can insert lines into our
SQL server database. The following string is sent by Internet Explorer to
the database where it updates the relevant table -

INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
'8', '01/05/2005 08:54:10', 'HOME\username')

The 2nd date is sent as a string into an nvarchar field so it causes no
problems but the first (which is heading for a datetime field) is assumed by
SQL to be MM/dd/yyyy format no matter what I try to do. All regional
settings are set to UK English & the table in SQL correctly uses dd/MM/yyyy
format so the above insert command fails thinking that I'm trying to add a
date of the 5th of month 15.

If I try input a date as mm/dd/yyyy format into the aspx page, an error is
thrown back.

Any ideas as to what's going wrong ?

Thanks

Steve


 
Reply With Quote
 
 
 
 
greybeard
Guest
Posts: n/a
 
      05-01-2005
You can set date in an universal format 'yyyymmdd',
or use convert(datetime,'dd/mm/yyyy',103) function, see MS SQL Help or
http://www.karaszi.com/SQLServer/info_datetime.asp

Vlastik

"Steve" <(E-Mail Removed)0m> píše v diskusním příspěvku
news:(E-Mail Removed)...
> Hi,
>
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
>
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
>
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed

by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses

dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
>
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
>
> Any ideas as to what's going wrong ?
>
> Thanks
>
> Steve
>
>



 
Reply With Quote
 
 
 
 
Uri Dimant
Guest
Posts: n/a
 
      05-01-2005
Steve
Always use 'YYYYMMDD' to insert data into SQL Server table. To display dates
use FORMAT or other functions to format to be suitable to the client.


"Steve" <(E-Mail Removed)0m> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
>
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
>
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed

by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses

dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
>
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
>
> Any ideas as to what's going wrong ?
>
> Thanks
>
> Steve
>
>



 
Reply With Quote
 
Jacco Schalkwijk
Guest
Posts: n/a
 
      05-01-2005
Date formats are set on the connection level in SQL Server, not database or
server wide. If they are not explicitly set, they are derived from the
default settings for the login that uses the connection. It appears to me
that the login you use to connect to the database has it's language (which
also includes the date format)set to British, but the login that you web app
uses to connect to the database, has it language set to English, i.e. U.S.
English.

--
Jacco Schalkwijk
SQL Server MVP


"Steve" <(E-Mail Removed)0m> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
>
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
>
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
>
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
>
> Any ideas as to what's going wrong ?
>
> Thanks
>
> Steve
>
>



 
Reply With Quote
 
Steve
Guest
Posts: n/a
 
      05-01-2005
Thanks Jacco - You got it in one. You have no idea how long I've been trying
to fix this!!!

I'd set a local account up on the server for testing things - trust
Microsoft to default things to 'english' which being from England myself I
would have assumed to be correct rather than having to choose 'British
English' !

Steve


"Jacco Schalkwijk" <(E-Mail Removed) > wrote
in message news:(E-Mail Removed)...
> Date formats are set on the connection level in SQL Server, not database
> or server wide. If they are not explicitly set, they are derived from the
> default settings for the login that uses the connection. It appears to me
> that the login you use to connect to the database has it's language (which
> also includes the date format)set to British, but the login that you web
> app uses to connect to the database, has it language set to English, i.e.
> U.S. English.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Steve" <(E-Mail Removed)0m> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>>
>> I've written a short aspx file so that end users can insert lines into
>> our SQL server database. The following string is sent by Internet
>> Explorer to the database where it updates the relevant table -
>>
>> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
>> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0',
>> '77', '8', '01/05/2005 08:54:10', 'HOME\username')
>>
>> The 2nd date is sent as a string into an nvarchar field so it causes no
>> problems but the first (which is heading for a datetime field) is assumed
>> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
>> settings are set to UK English & the table in SQL correctly uses
>> dd/MM/yyyy format so the above insert command fails thinking that I'm
>> trying to add a date of the 5th of month 15.
>>
>> If I try input a date as mm/dd/yyyy format into the aspx page, an error
>> is thrown back.
>>
>> Any ideas as to what's going wrong ?
>>
>> Thanks
>>
>> Steve
>>
>>

>
>



 
Reply With Quote
 
Mercury
Guest
Posts: n/a
 
      05-01-2005
I suggest that you read up about SQL Code Injection once you have this
resolved. The implication from your question is that data is more or less
coming off a web form straight into the database and as such may be highly
vulnerable to hacking.


"Steve" <(E-Mail Removed)0m> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
>
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
>
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
>
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
>
> Any ideas as to what's going wrong ?
>
> Thanks
>
> Steve
>
>



 
Reply With Quote
 
Cor Ligthert
Guest
Posts: n/a
 
      05-01-2005
>
> I'd set a local account up on the server for testing things - trust
> Microsoft to default things to 'english' which being from England myself I
> would have assumed to be correct rather than having to choose 'British
> English' !
>

LOL


 
Reply With Quote
 
Dan Guzman
Guest
Posts: n/a
 
      05-01-2005
The original poster might consider a parameterized query. This will address
both the SQL injection security issue as well as date string formatting.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mercury" <(E-Mail Removed)> wrote in message
news:d52dsi$ac7$(E-Mail Removed)...
>I suggest that you read up about SQL Code Injection once you have this
>resolved. The implication from your question is that data is more or less
>coming off a web form straight into the database and as such may be highly
>vulnerable to hacking.
>
>
> "Steve" <(E-Mail Removed)0m> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>>
>> I've written a short aspx file so that end users can insert lines into
>> our SQL server database. The following string is sent by Internet
>> Explorer to the database where it updates the relevant table -
>>
>> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
>> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0',
>> '77', '8', '01/05/2005 08:54:10', 'HOME\username')
>>
>> The 2nd date is sent as a string into an nvarchar field so it causes no
>> problems but the first (which is heading for a datetime field) is assumed
>> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
>> settings are set to UK English & the table in SQL correctly uses
>> dd/MM/yyyy format so the above insert command fails thinking that I'm
>> trying to add a date of the 5th of month 15.
>>
>> If I try input a date as mm/dd/yyyy format into the aspx page, an error
>> is thrown back.
>>
>> Any ideas as to what's going wrong ?
>>
>> Thanks
>>
>> Steve
>>
>>

>
>



 
Reply With Quote
 
Steve
Guest
Posts: n/a
 
      05-01-2005
> The original poster might consider a parameterized query. This will
> address both the SQL injection security issue as well as date string
> formatting.


I may go on to look at that but I'm currently not too bothered about hacking
attempts as the form is for intranet use only - I just need something quick
& dirty !

Thanks

Steve


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sql timeout from web application problem (works good in sql server dave ASP .Net 2 12-04-2008 02:42 PM
SQL server 2000 -> JDBC Date Time Format Problem mukesh bhakta Java 3 09-05-2006 04:16 PM
Insert Date and Time in SQL Server 2000 using ASP Robin Lawrie ASP General 18 03-01-2005 03:37 PM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Date Format - best way of converting a string into a date format Brian Candy ASP .Net 2 02-18-2004 02:13 PM



Advertisments