Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases 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 February 16th, 2005, 08:47 AM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL dates - Problem

Can anyone please help.... i have a SQL 2000 database... i am searching for dates in a SQL select. ie

SELECT * FROM Tbl WHERE ID = 12 AND StartDate = 12/02/2005

But even through SQL query analyzer, it returns 0 results, even though there is records that meet this criteria... I know it is summit to do with the Date, but cant figure it out... The startdate field is in Date/Time Format in sql... Can anyone give me an idea on why this doesnt work

Cheers


Reply With Quote
  #2 (permalink)  
Old February 16th, 2005, 10:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

use the CONVERT function to avoid any ambiguity:
SELECT * FROM Tbl WHERE ID = 12
AND StartDate = CONVERT(datetime, '12/02/2005', ?)
- see I don't know what to put at the end, coz I don't know whether your date is supposed to be 12 Feb 2005, or 2 Dec 2005...

hth
Phil
Reply With Quote
  #3 (permalink)  
Old February 16th, 2005, 10:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
Thanks: 0
Thanked 3 Times in 3 Posts
Default

One of these might work:
Style ID Style Type
0 or 100 - mon dd yyyy hh:miAM (or PM)
101 - mm/dd/yy
102 - yy.mm.dd
103 - dd/mm/yy
104 - dd.mm.yy
105 - dd-mm-yy
106 - dd mon yy
107 - Mon dd, yy
108 - hh:mm:ss
9 or 109 - mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 - mm-dd-yy
111 - yy/mm/dd
112 - yymmdd
13 or 113 - dd mon yyyy hh:mm:ss:mmm(24h)
114 - hh:mi:ss:mmm(24h)
20 or 120 - yyyy-mm-dd hh:mi:ss(24h)
21 or 121 - yyyy-mm-dd hh:mi:ss.mmm(24h)
126 - yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 - dd mon yyyy hh:mi:ss:mmmAM
131 - dd/mm/yy hh:mi:ss:mmmAM
Reply With Quote
  #4 (permalink)  
Old February 16th, 2005, 12:08 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply guys...
I am using DD/MM/YYYY
The value passed is a querystring value (Using ASP), which is called DateString.. The field is a datetime field

I have this now, but still nothing is being returned:

SQL = "SELECT * FROM Meta_Default WHERE ID = 14 AND StartDate = CONVERT(datetime, '" & trim(DateString) & "', 103)"

Anyone got any ideas now

Cheers

Reply With Quote
  #5 (permalink)  
Old February 16th, 2005, 12:18 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Forget the last post guys, i have it sorted.. Thanks very much for your time and effort, true gents :o)

thanks

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
help with SQL dates Byock SQL Server 2000 1 March 23rd, 2006 11:58 AM
Getting Dates in SQL meichmann SQL Server 2000 5 April 18th, 2005 10:58 AM
SQL Problem in access with dates tucker SQL Language 4 December 19th, 2003 11:16 AM
(one last problem)... can't insert dates in SQL nikosdra SQL Server ASP 1 July 28th, 2003 03:43 PM
International Dates in SQL owain SQL Language 2 June 13th, 2003 03:12 PM



All times are GMT -4. The time now is 12:36 AM.


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