UK date format query?

Databases
1

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

try using CONVERT instead of CAST

CONVERT(DATE,matchdate,103)
3

Hi r937

Thank you for your response.

I tried using your CONVERT solution but I still got the same error.

Microsoft OLE DB Provider for SQL Server error ‘80040e07’

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

My script now looks like this:

SET DATEFORMAT DMY; SELECT * from mytable
WHERE CONVERT(DATE,matchdate,103) BETWEEN ‘" & startdate & "’ and ‘" & enddate & "’
order by cast([matchdate] as datetime) asc"

Any ideas?

Best regards

Rod from the UK

4

perhaps change the other CAST to CONVERT too

:slight_smile:

5

Hi r937

I tried that too. I even tried removing that line altogether and still got the same message.

Regards

Rod

6

okay, that means there is at least one row in your data which does not have a valid date

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

7

Excellent - it now works perfectly!

Thank you so much!

Nest regards

Rod from the UK

8

I think some of your date has the MDY format instead of DMY

9

couple days late and at least a dollar short

:slight_smile:

10