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.
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
Rod from the UK