Professional Web Applications Themes

Date Format Problem - SQL Server Insert From Web Application - ASP.NET Web Services

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

  1. #1

    Default Date Format Problem - SQL Server Insert From Web Application

    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


    Steve Guest

  2. #2

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    You can set date in an universal format 'yyyymmdd',
    or use convert(datetime,'dd/mm/yyyy',103) function, see MS SQL Help or
    [url]http://www.karaszi.com/SQLServer/info_datetime.asp[/url]

    Vlastik

    "Steve" <steve.hendersonbtinternet.c0m> píše v diskusním příspěvku
    news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >

    greybeard Guest

  3. #3

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    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" <steve.hendersonbtinternet.c0m> wrote in message
    news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >

    Uri Dimant Guest

  4. #4

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    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" <steve.hendersonbtinternet.c0m> wrote in message
    news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >

    Jacco Schalkwijk Guest

  5. #5

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    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" <jacco.please.replyto.newsgroups.mvps.org.invalid > wrote
    in message news:u39qoyjTFHA.3184TK2MSFTNGP15.phx.gbl...
    > 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" <steve.hendersonbtinternet.c0m> wrote in message
    > news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    >> 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
    >>
    >>
    >
    >

    Steve Guest

  6. #6

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    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" <steve.hendersonbtinternet.c0m> wrote in message
    news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    > 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
    >
    >

    Mercury Guest

  7. #7

    Default Re: Date Format Problem - SQL Server Insert From Web Application

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


    Cor Ligthert Guest

  8. #8

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    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" <mespam.com> wrote in message
    news:d52dsi$ac7$1lust.ihug.co.nz...
    >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" <steve.hendersonbtinternet.c0m> wrote in message
    > news:uBIy7ljTFHA.2520TK2MSFTNGP09.phx.gbl...
    >> 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
    >>
    >>
    >
    >

    Dan Guzman Guest

  9. #9

    Default Re: Date Format Problem - SQL Server Insert From Web Application

    > 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


    Steve Guest

Similar Threads

  1. VB Date functions into ISO format for SQL server
    By newedgemedia.co.uk in forum Dreamweaver AppDev
    Replies: 1
    Last Post: February 28th, 01:38 PM
  2. I still have problem of date format
    By rick rick in forum ASP Database
    Replies: 3
    Last Post: January 15th, 05:53 AM
  3. Insert date to SQL server creates error
    By Ross Francis in forum ASP Database
    Replies: 1
    Last Post: September 2nd, 12:22 AM
  4. #24966 [NEW]: Date format problem
    By f dot ledoeuff at hud dot ac dot uk in forum PHP Development
    Replies: 0
    Last Post: August 6th, 02:46 PM
  5. Date Format for other locale settings SQL Server
    By Odelia in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 9th, 06:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139