Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 22nd, 2009, 05:26 AM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default he conversion of a char data type to a datetime data type resulted in an out-of-range

Hi there

I'm getting the following error:

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

This is the statement that's causing it:

SELECT p.productid, p.subcategoryid, p.productname, p.partnumber, p.rrp, p.cost, p.enddate, p.weight, p.tagline, LEFT(CAST(p.extrainformation as varchar(100)), 100) AS 'extrainformation', p.imagename, p.imagename_m, p.imagename2, p.imagename3, st.id, st.stock, p.onspecial, 1 As fIsSpecial, p.sequence As sequence FROM product p, tbl_stock st WHERE DATEDIFF(day, p.enddate, GETDATE()) < 0 AND p.deleted = 0 AND p.hide = 0 AND st.id = p.stock AND p.enddate = '31/12/2009' AND p.promotionid = '9' ORDER BY fIsSpecial DESC, sequence

I'm not sure how to go about fixing it?

many thanks

Adam
Reply With Quote
  #2 (permalink)  
Old December 23rd, 2009, 03:27 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

It is not picking the data format correctly.

p.enddate = '31/12/2009'

change above to

p.enddate = '12/31/2009'
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
Reply With Quote
  #3 (permalink)  
Old December 23rd, 2009, 04:29 AM
Authorized User
Points: 405, Level: 7
Points: 405, Level: 7 Points: 405, Level: 7 Points: 405, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2009
Posts: 85
Thanks: 16
Thanked 0 Times in 0 Posts
Default he conversion of a char data type to a datetime data type resulted in an out-of-range

Hi there

thanks for your reply - but p.enddate is in the format 31/12/2009?

Should it be different?

thanks

Adam
Reply With Quote
  #4 (permalink)  
Old December 23rd, 2009, 07:18 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

To Use DMY format you can try the following:
SET DATEFORMAT Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.
Code:
SET DATEFORMAT dmy;
SELECT p.productid, p.subcategoryid, p.productname, p.partnumber, p.rrp, p.cost, p.enddate, p.weight, p.tagline, LEFT(CAST(p.extrainformation as varchar(100)), 100) AS 'extrainformation', p.imagename, p.imagename_m, p.imagename2, p.imagename3, st.id, st.stock, p.onspecial, 1 As fIsSpecial, p.sequence As sequence FROM product p, tbl_stock st WHERE DATEDIFF(day, p.enddate, GETDATE()) < 0 AND p.deleted = 0 AND p.hide = 0 AND st.id = p.stock AND p.enddate = '31/12/2009' AND p.promotionid = '9' ORDER BY fIsSpecial DESC, sequence
SET DATEFORMAT mdy;
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Char Data Type to smalldatetime rylemer SQL Language 3 March 3rd, 2005 03:13 PM
Help with data type conversion please androoo Pro VB 6 2 December 24th, 2004 05:45 PM
Help with data type conversion please androoo Pro VB.NET 2002/2003 0 November 29th, 2004 09:06 AM
Data Type Conversion using WHERE IN () Colonel Angus SQL Server 2000 14 August 18th, 2004 09:08 PM
Data Type Conversion owain SQL Language 5 October 31st, 2003 12:31 PM



All times are GMT -4. The time now is 05:04 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.