date mystery

[meltedown]

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)

[Steve]

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)

Uh. Dunno. What are you typing it into? What language do you think it
is? It looks a bit like a MySQL query, but mangled; is that what you
intended? If it is, why are there single quotes around the first
argument to DATE_SUB()? Is there another DATE_SUB() that expects a
string rather than a date? Yes, it's definitely a mystery...

---
Steve

[Oli Filth]

meltedown wrote:

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)

I would imagine it's because you have quotes around
FROM_DAYS(TO_DAYS(2005-09-28 18:04:19)).

--
Oli

[meltedown]

Steve wrote:

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)

Uh. Dunno. What are you typing it into? What language do you think it
is? It looks a bit like a MySQL query, but mangled; is that what you
intended? If it is, why are there single quotes around the first
argument to DATE_SUB()? Is there another DATE_SUB() that expects a
string rather than a date? Yes, it's definitely a mystery...

---
Steve

Its mysql.

If I take the quotes out, I get an error
query failed:1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '18:04:19)), INTERVAL 6 DAY)' at line 1

query was:
SELECT DATE_SUB(FROM_DAYS(TO_DAYS(2005-09-28 18:04:19)), INTERVAL 6 DAY)

I'm trying to get the date that's a week before the date in the query.

[Gordon Burditt]

>> Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)

'FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))' is not a valid date.
Valid dates don't have a bunch of underscores, parentheses, and alphabetic
junk in them.

You might want:

SELECT DATE_SUB(FROM_DAYS(TO_DAYS('2005-09-28 18:04:19')), INTERVAL 6 DAY)

but I'm not sure I understand what the point of the FROM_DAYS and TO_DAYS
calls is.

Gordon L. Burditt

[Oli Filth]

meltedown said the following on 04/10/2005 18:40:

Steve wrote:

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6
DAY)

Uh. Dunno. What are you typing it into? What language do you think it
is? It looks a bit like a MySQL query, but mangled; is that what you
intended? If it is, why are there single quotes around the first
argument to DATE_SUB()? Is there another DATE_SUB() that expects a
string rather than a date? Yes, it's definitely a mystery...

Its mysql.

If I take the quotes out, I get an error
query failed:1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '18:04:19)), INTERVAL 6 DAY)' at line 1

query was:
SELECT DATE_SUB(FROM_DAYS(TO_DAYS(2005-09-28 18:04:19)), INTERVAL 6 DAY)

I'm trying to get the date that's a week before the date in the query.

If you had bothered to RTFM, you would've found that you can put the
date straight into DATE_SUB (seeing as FROM_DAYS(TO_DAYS()) gets you
back where you started, assuming you had the syntax correct).

Note also that a week is 7 days long!

SELECT DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY)

If you don't want the time in the result, then cast it to a DATE, i.e.:

SELECT DATE(DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY))
--
Oli

[meltedown]

Oli Filth wrote:

meltedown said the following on 04/10/2005 18:40:

Steve wrote:

Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL
6 DAY)


Uh. Dunno. What are you typing it into? What language do you think it
is? It looks a bit like a MySQL query, but mangled; is that what you
intended? If it is, why are there single quotes around the first
argument to DATE_SUB()? Is there another DATE_SUB() that expects a
string rather than a date? Yes, it's definitely a mystery...

Its mysql.

If I take the quotes out, I get an error
query failed:1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '18:04:19)), INTERVAL 6 DAY)' at line 1

query was:
SELECT DATE_SUB(FROM_DAYS(TO_DAYS(2005-09-28 18:04:19)), INTERVAL 6 DAY)

I'm trying to get the date that's a week before the date in the query.

If you had bothered to RTFM, you would've found that you can put the
date straight into DATE_SUB (seeing as FROM_DAYS(TO_DAYS()) gets you
back where you started, assuming you had the syntax correct).

I've tried to read the date functions section but it is written in
neo-colonial greek. That's early greek, before Athens was even a city.
I've got all the best books, and they aren't much better.
Note also that a week is 7 days long!

SELECT DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY)

If you don't want the time in the result, then cast it to a DATE, i.e.:

SELECT DATE(DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY))

Thanks, that seems to work. The other worked for a long time, I don't
know why it would work and then not work. The only thing different was
the date.

[Oli Filth]

meltedown said the following on 04/10/2005 22:50:

Oli Filth wrote:

meltedown said the following on 04/10/2005 18:40:

> Why doesn't this return anything ?
>
> SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL
> 6 DAY)
I'm trying to get the date that's a week before the date in the query.

If you had bothered to RTFM, you would've found that you can put the
date straight into DATE_SUB (seeing as FROM_DAYS(TO_DAYS()) gets you
back where you started, assuming you had the syntax correct).

I've tried to read the date functions section but it is written in
neo-colonial greek. That's early greek, before Athens was even a city.
I've got all the best books, and they aren't much better.

I agree, the manual is somewhat dense and cryptic, but there are *lots*
of examples, including one as follows:

SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY)

Note also that a week is 7 days long!

SELECT DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY)

If you don't want the time in the result, then cast it to a DATE, i.e.:

SELECT DATE(DATE_SUB('2005-09-28 18:04:19', INTERVAL 7 DAY))

Thanks, that seems to work. The other worked for a long time, I don't
know why it would work and then not work. The only thing different was
the date.

Your original query (with the quotes where they were) could never have
worked, as 'FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))' is not a valid date
string. ;)

All it does is truncate the invalid date string to nothing, and hence
you will get a NULL return value.
--
Oli