asked on

Out-of-range datetime value

<cfset datepassed = #DATEFORMAT(now(), "dd/mm/yy")#>

SELECT CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate
FROM orders
WHERE YEAR(TIMEDATE) = YEAR('#datepassed#')
AND MONTH(TIMEDATE) = MONTH('#datepassed#')
AND DAY(TIMEDATE) = DAY('#datepassed#')

ERROR:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

DBAduck - Ben Miller

Whenever you use Datetime, you can either use mm/dd/yyyy or you can use a string of yyyymmdd.

If you are using a machine that has the Regional settings like UK then they will want the dd/mm/yyyy settings, then I would use the universal format yyyymmdd instead.

DBAduck - Ben Miller

And it is DATETIME, not TIMEDATE.

Mark Wills

OK, the CONVERT(VARCHAR(10), TIMEDATE , 120) is using a format number. That number is telling convert how to read/ interpret the variable TIMEDATE. Now if TIMEDATE is a legitimate datetime column, then no problems, it knows how to format. However, when using a string / variable such as datepassed, then it has to understand how it is formatted... So, before you can use datetime functions such as year, month, day, then it has to recognise the string as a date...

By the looks of things, you do not want time as a component, so, probably best to do something like :

SELECT CONVERT(VARCHAR(10), TIMEDATE , 120) as varDate
FROM orders
WHERE convert(datetime,CONVERT(varchar(20), TIMEDATE , 106)) = convert(datetime,'#datepassed#',103)

basically allowing the datetime to do it's match without the time component, and expressing both fields as a legitimate datetime construct.

pigmentarts

ASKER

thank you for all your help

pigmentarts

ASKER

mark_aills:

using your code i still get this...

Syntax error converting datetime from character string.

Mark Wills

use a 4 digit year in datepassed

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.