• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

SQL Date Input


I'm using a shared SQL server.

I'm UK based

I have a form with a datepicker on it, and being in the UK want to display the date picked as dd/mm/yy or dd/mm/y

However when I try to do either an insert or an update on the database with a date greater than the 12th - 14/10/11 for instance -

I get --

"Microsoft OLE DB Provider for SQL Server error '80040e07'
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
/direct/includes/product-update.asp, line 188"

I am asuming this is because the database is expecting the date input in the form of MM/DD/YYY and as there are not 14 months.....

So how do i work around this? I need my form to display UK format (dd/mm/yyy) the database needs MM/DD/YYYY?

I'm developing using ASP VBscript on a MS SQL 2008 database...

Insert & Update are Stored Procedures..

Thank you
2 Solutions
Lee SavidgeCommented:
I assume the date comes into the sp as a string and the sp is casting it.

If so, rather than use cast, use convert:

convert(datetime, @mydatestring, 103)

103 assumes dd/mm/yyyy

if you wand dd/mm/yy then use 3

Please see this article:


Also before insert or update statement do set DateFormat that suites your requirement, for e.g.

set DateFormat dmy -- Defines datetime as on Date Month Year format
insert into sometable (name, datetime) values ('testing', '14/10/11')
garethtnashAuthor Commented:
Thank you both :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now