SQL Date Input

Posted on 2011-10-05
Last Modified: 2012-05-12

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
Question by:garethtnash
    LVL 25

    Accepted Solution

    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

    LVL 15

    Assisted Solution

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

    Author Closing Comment

    Thank you both :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now