By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

date mystery

P: n/a
Why doesn't this return anything ?

SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)
Oct 4 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Oct 4 '05 #2

P: n/a

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

Oct 4 '05 #3

P: n/a
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.
Oct 4 '05 #4

P: n/a
>> 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
Oct 4 '05 #5

P: n/a
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
Oct 4 '05 #6

P: n/a
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.
Oct 4 '05 #7

P: n/a
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
Oct 4 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.