select rows from a date

sidsel

I have a column with a date in the following format: '06-17-2004 19:17:52'

How do I select all rows where the date is 06-17-2004 no matter what the time says?

eoghain

You need to either use BETWEEN or add another WHERE clause constraint:

//Either
SELECT *
  FROM MyTable
 WHERE MyDate Between '06-17-2004' AND '06-18-2004'

//Or
SELECT *
  FROM MyTable
 WHERE MyDate >= '06-17-2004'
    OR MyDate <= '06-18-2004'

When you supply a date with no time information to a date/time field or timestamp field the time information is assumed to be 00:00:00 so you ned to select from the first day you want at 00:00:00 to the day after the last day you want.

nemonoman

my guess is that you're not using a DATE_TIME type column to store this info. Silly, Silly Wabbit...

WHY DO PEOPLE DO THIS? Database date management is SO POWERFUL, WHY, WHY do people insist on making up half-assed alternatives???

You've set up a string; you're querying a string, so you'll have to use a string type query...

SELECT *
FROM MyTable
WHERE MyDate LIKE '06-17-2004%'

Why you don't want set up dates like '06-17-2004':

Test this query:

SELECT IF( '06-17-2004' > '01-01-1901', 'I am an idiot', 'no, I am very smart' )

As you can see: according to your scheme '06-17-2004' is GREATER THAN '01-01-1901'

sidsel

It's originally an access database table thats why the date is stored in that format.

nemonoman

Create a DATE_TIME column

Assuming your half-assed date column is in the format
'06-17-2004 19:17:52'

This code will create a real date_time from that data

UPDATE mytable
SET myReasonableDateColumn=
CONCAT( SUBSTRING( myHalfassedDateTime,
7,
4 ),
SUBSTRING( myHalfassedDateTime,
1,
2 ),
SUBSTRING( myHalfassedDateTime,
4,
2 ),
SUBSTRING( myHalfassedDateTime,
12,
2 ),
SUBSTRING( myHalfassedDateTime,
15,
2 ),
SUBSTRING( myHalfassedDateTime,
18,
2 ) )

You'll thank me someday.

mourisj

I'm not sure that this is not a date_time field! Maybe this date format is just a matter of settings!

This can be influenced by settings e.g. the NLS_LANGUAGE parameter (don't know the name exactly) in Oracle: at the office, my Oracle tools always display dates in a format like '18-jun-2004 20:00:00', while the related applications display the data like '18.06.2004' '20:00'.

I guess, this may also be influenced by theI nternational settings on Windows, or by parameters loaded at database startup. Check the documentation about this.

Btw., BETWEEN includes both values provided (at least for Oracle)