Date -10

[pandreozzi]

Hi All. I would like to extract table date using a date but the key is if the table data is not older than 10 days I would like to drop that. In essence 11+ days is when I need to bring the table date into my chart. The reason for the -10 is as an example taking into consideration today's date minus 10 days than start pulling table data. I hope this makes sense.

Any help is appreciated.

[MarcoARaymundo]

Try ...

LOAD

     Fields

FROM [TABELA]

WHERE DateField >= (Today() - 10)

[pandreozzi]

Thanks Marco. I just found that the date is stored at text. Can anyone show me how to use the string to date function in the select statement to convert the text version of the date to a real date. The current format is:

DD-MMM-YY

I would like to convert it to

MM-DD-YYYY

[tresesco]

Use date#() to covert the string/text to proper date and date() to format it to desired format. Try like:

Date(Date#(DateField, 'DD-MMM-YY'), 'MM-DD-YYYY')

[pandreozzi]

Tresesco, thanks for your reply. However this does not work in the select statement only on the load statement. I need to convert this out of the DB as I follow up with a case statement that will use the date in the findings.

[tresesco]

Select statement gets executed in your DB rather than qv. Therefore, you may use the similar date parsing functions that your DB supports, like in Oracle TO_DATE().

[pandreozzi]

agree as I am using the followingstr_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

str_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

This does not seem to work and I have also tried:
date_format str_to_date(a.date, '%d-%m-%y'), '%d/%m/%y') NEWNAME,

[tresesco]

I am not expert in MySql, but it seems that abbreviated month is parsed there by '%b'. Try like:

str_to_date(a.date, '%d-%b-%y')

And yes, be careful about brackets.

[pandreozzi]

ok. I will try that and let you know. I took my format off stackoverflow web site

[pandreozzi]

Tresesco, that worked. who knew that d (month) is actually m(month) appreciate your assistance.