Another SQL Problem

Kai

Hi,

Right i have a Sybase DB with a table that i need to pull data from. Theres a Column there with the date the item was created and i want to be able to usa a WHERE clause to just pull the date i want.

This looks pretty basic but it doesnt work.

SELECT * FROM dbo.MYtable WHERE created_date LIKE %19/10/2004%

SELECT * FROM dbo.MYtable WHERE created_date = #19/10/2004#

SELECT * FROM dbo.MYtable WHERE created_date = '19/10/2004'

Ive been trying variations of all of these with no success. either empty recordsets or Errors about incorrect syntax.

The created_date format is 18/10/2004 08:12 and i want to be able to search by the Date and leav out the Time part.


Any ideas ??

McGintyMcGoo

I've never used Sybase before but I presume that you'll need to convert the date format of the data in the table to dd/mm/yyyy. It's probably trying to compare the time aswell which is why you are not getting any results.

ter

try

SELECT * FROM dbo.MYtable WHERE created_date LIKE '%19/10/2004%';

maybe not sure

HTH

ter

or date is > than date and < than date

smidgy

Try SELECT * FROM dbo.MYtable WHERE created_date = 18/10/2004 08:12

to see if this works and that the syntax is right....

if not try fiddling the syntax or switching to 10/18/2004 08:12 (i know with SQL server dates are fiddly like this - stored and queried differently)

In sql serv

jd

I would suggest if it is possible (not familiar with sysbase though), explicitly set the date format first for the session, and then do your query.

http://www.analysisandsolutions.com/code/dates.htm

scargill

SELECT * FROM dbo.MYtable
WHERE date(created_date) = '2004-10-18'

Kai

I had to use ASP to append "00:00.00" and "12:00.00" to 2 variables and then use
SELECT *
FROM Blah
WHERE created_date > '"&StartDate&"' AND created_date < '"&EndDate&"'


Which seemed to work.