0

Here is the query

INSERT INTO GST_Chp 
    VALUES('Date envoi', '__001_commande Achat', '', 'DATE', 'False', '', '16/3/2016', '8')

Insert correctly works in SQL Server, but when my web app tries to run it, a

"Conversion from string to date operation resulted into an out of range result"

error occurs. Thanks for any help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prince Gnakou
  • 87
  • 1
  • 3
  • 12
  • 3
    it's probably because of the language of the webapp. Try inserting `'20160316'` since it's a non ambiguous way to insert dates – Lamak Mar 16 '16 at 16:22
  • 1
    [Read here for date time formats](http://stackoverflow.com/a/34275965/5089204) – Shnugo Mar 16 '16 at 16:25
  • 1
    Alternatively, the ODBC-invariant way for specifying a date literal is `{d '2016-03-16'}`. But even better than all of that is to read up on how you use parameterized queries, so you don't run into this problem and avoid SQL injection to boot. It sounds like your web application has a potentially serious security issue. – Jeroen Mostert Mar 16 '16 at 16:26

1 Answers1

0

It'll be due to the user context that the query is running in and the date field. Never, ever put dates in your queries as dd/mm/yyyy; it's not reliable, as you've discovered.

If you have to put them directly in a query text like that, use yyyy-mm-dd and you're normally safe. Ideally use a parameter object and pass the values that way though; much more reliable.

eftpotrm
  • 2,241
  • 1
  • 20
  • 24
  • 3
    `yyyy-MM-dd` is unfortunately not safe either. Try `SET LANGUAGE FRENCH` before and watch it burn. `yyyyMMdd` is safe, so is `{d 'yyyy-MM-dd'}`. – Jeroen Mostert Mar 16 '16 at 16:28
  • @JeroenMostert Thanks! I knew I'd seen something odd very occasionally (hence 'normally safe') but couldn't remember the details. Thanks for clarifying, very useful. – eftpotrm Mar 16 '16 at 16:32