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

SQL Date Input

Hello,

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
0
garethtnash
Asked:
garethtnash
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

0
 
pateljituCommented:
Please see this article:

http://msdn.microsoft.com/en-us/library/aa259188%28v=sql.80%29.aspx

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')
0
 
garethtnashAuthor Commented:
Thank you both :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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