Solved

Out-of-range datetime value

Posted on 2008-06-16
8
1,972 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

14 Experts available now in Live!

Get 1:1 Help Now