Solved

Out-of-range datetime value

Posted on 2008-06-16
8
1,973 Views
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#')

ERROR:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
Comment
Question by:pigmentarts
  • 3
  • 3
  • 2
8 Comments
 
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 21797916
And it is DATETIME, not TIMEDATE.
0
 
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 :

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.
0
 
LVL 12

Author Comment

by:pigmentarts
ID: 21810992
thank you for all your help
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 12

Author Comment

by:pigmentarts
ID: 21812932
mark_aills:

using your code i still get this...

Syntax error converting datetime from character string.
0
 
LVL 51

Expert Comment

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

Accepted Solution

by:
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           yy.mm.dd

3                         103                  British/French dd/mm/yy

4                         104                  German         dd.mm.yy        

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

0
 
LVL 12

Author Comment

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot not using aggregate yield error 3 41
How to simplify my SQL statement? 14 50
SqlAdvisor 2016 3 28
Stored Procedure 2 47
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now