Out-of-range datetime value

Posted on 2008-06-16
Last Modified: 2008-06-18
      <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#')


The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Question by:pigmentarts
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 21797913
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.
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 21797916
LVL 51

Expert Comment

by:Mark Wills
ID: 21810433
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 :

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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 12

Author Comment

ID: 21810992
thank you for all your help
LVL 12

Author Comment

ID: 21812932

using your code i still get this...

Syntax error converting datetime from character string.
LVL 51

Expert Comment

by:Mark Wills
ID: 21813417
use a 4 digit year in datepassed
LVL 51

Accepted Solution

Mark Wills earned 500 total points
ID: 21813502
this works : select convert(datetime,'21/01/2008',103)
this fails : select convert(datetime,'21/01/08',103)

this works: select convert(datetime,'21/01/08',03)
this fails : select convert(datetime,'21/01/2008',03)

even though the books online show it as yy, it isn't really... remember how I said it must match - it is really , really fussy....

have a look at the codes below...

Without century (yy)      With century (yyyy)  Standard       Input/Output 
-                         0 or 100             Default        mon dd yyyy hh:miAM (or PM)
1                         101                  U.S.           mm/dd/yyyy
2                         102                  ANSI 
3                         103                  British/French dd/mm/yy
4                         104                  German        
5                         105                  Italian        dd-mm-yy
6                         106                  -              dd mon yy
7                         107                  -              Mon dd, yy
8                         108                  -              hh:mm:ss
-                         9 or 109             Default + ms   mon dd yyyy hh:mi:ss:mmmAM (or PM)
10                        110                  USA            mm-dd-yy
11                        111                  JAPAN          yy/mm/dd
12                        112                  ISO            yymmdd
-                         13 or 113            Europe         dd mon yyyy hh:mm:ss:mmm(24h)
14                        114                  -              hh:mi:ss:mmm(24h)
-                         20 or 120            ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
-                         21 or 121            ODBC canonical yyyy-mm-dd hh:mi:ss.mmm(24h)
-                         126                  ISO8601        yyyy-mm-ddThh:mm:ss.mmm (no spaces)
                          127                  ISO8601        yyyy-mm-ddThh:mm:ss.mmmZ   where Z = time zone(no spaces)               
-                         130                  Hijri          dd mon yyyy hh:mi:ss:mmmAM
-                         131                  Hijri          dd/mm/yy hh:mi:ss:mmmAM

Open in new window

LVL 12

Author Comment

ID: 21813602
i see what you mean now. just had to get my head around it. working now thanks

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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