Thread: UK date query?

    #1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2014
    Posts
    67
    Rep Power
    4

    UK date query?


    Hi

    I hope this is solvable.

    Basically, I'm working with a nSQLserver database table and in this table there is a field called "matchdate". The format of the field is nchar(50).

    There are over 2000 records in my table and the dates in the "matchdate" field are all in UK format, for example, "29/04/2014".

    I am trying to query the table so I can pull out records between two UK dates. To do this I am using the following query:

    --------------------------------------
    SET DATEFORMAT DMY; SELECT * from mytable
    WHERE CAST(matchdate as datetime) BETWEEN '" & startdate & "' and '" & enddate & "'
    order by cast([matchdate] as datetime) asc"
    --------------------------------------

    As you can probably tell this type of query is certainly not my strength. If the UK "startdate" value is "01/03/2014" and the UK enddate value is "23/04/2014" I get the following error:

    ------------------------------------
    Microsoft OLE DB Provider for SQL Server error '80040e07'

    Conversion failed when converting date and/or time from character string.

    /mypage.asp,

    ------------------------------------

    I'm guessing this is because I am using two UK dates formats? How do I query the UK formatted "matchdate" field table using UK "start" and UK "end" formatted dates and get around this error?

    PS: Unfortunately, I do not have access to the database table.

    Any help would be fully appreciated

    Best regards

    Rod from the UK
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    283
    try that

    SET DATEFORMAT DMY; SELECT * from mytable
    WHERE CONVERT(DATETIME, matchdate,103) BETWEEN '" & startdate & "' and '" & enddate & "'
    order by cast([matchdate] as datetime) asc"
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2014
    Posts
    67
    Rep Power
    4
    Excellent - thanks GK!

    Best regards

    Rod from the UK

Similar Threads

  1. Date field in cross tab query shows as number instead of date
    By wardl in forum Microsoft Access Help
    Replies: 2
    Last Post: April 28th, 2013, 01:03 AM
  2. Matching Date of Query to Calendar Control Date
    By QLD Buses in forum Microsoft Access Help
    Replies: 10
    Last Post: February 7th, 2012, 02:38 AM
  3. Access Query: Can you Query by a Date Range?
    By the_master68 in forum Microsoft Access Help
    Replies: 2
    Last Post: April 8th, 2011, 12:43 PM
  4. Lookup Date from a Date Range and display in query results
    By marisol2 in forum Microsoft Access Help
    Replies: 3
    Last Post: September 23rd, 2009, 08:33 PM
  5. Query date from DateTime - General Date format field.
    By KeithAlan1 in forum Microsoft Access Help
    Replies: 7
    Last Post: March 14th, 2006, 01:03 PM

IMN logo majestic logo threadwatch logo seochat tools logo