Retrieving date field in '%m/%d/%Y' format using the query

Classic Servoy
1

Hi ,

Now i can able to display the date in a html format thats present inbetween two date field ,this can be done by getting the date field by a query(here i am gettting date field and other fields from four tables by four different query)and convert that into html by using the dataset.

Is it possible to do the same thats retrieve the date field in ‘%m/%d/%Y’ format means replace the current format 2006-11-06 00:00:00.0
to ‘%m/%d/%Y’ by using mysql and display them in the html area in servoy.

Any thoughts :idea: would be appreciated

2

Hi,

I believe you are looking for the DATE_FORMAT() function of MySQL.
Since I don’t know what version of MySQL you run here are the pages for 4.1, 5.0 and 5.1. Although I don’t think much has changed between them.

http://dev.mysql.com/doc/refman/4.1/en/ … tions.html
http://dev.mysql.com/doc/refman/5.0/en/ … tions.html
http://dev.mysql.com/doc/refman/5.1/en/ … tions.html

Hope this helps.

3

Exactly Robert ,

i tried by mean of the date_format() in the query.But the dataset is not allowing that format.Is it not possible to use this function in servoy.

4

That suprises me. I use database functions al over the place without any problems.
Can you post the code here that gives you trouble ?

5

ROCLASI:
Hi,

I believe you are looking for the DATE_FORMAT() function of MySQL.
Since I don’t know what version of MySQL you run here are the pages for 4.1, 5.0 and 5.1. Although I don’t think much has changed between them.

http://dev.mysql.com/doc/refman/4.1/en/ … tions.html
http://dev.mysql.com/doc/refman/5.0/en/ … tions.html
http://dev.mysql.com/doc/refman/5.1/en/ … tions.html

Hope this helps.

Wow Robert!!
You’re acting like a walking manual lately… :D

6

rifluxyss:
Exactly Robert ,

i tried by mean of the date_format() in the query.But the dataset is not allowing that format.Is it not possible to use this function in servoy.

I use the date_format() functions all the time in my MySQL queries and it works perfectly, look at this:

select ddtid as "ID DDT",date_format(data_creazione, "%d-%m-%Y %H:%i") as "Data Creaz.",creatore as "Creatore",provenienza as "Provenienza",concat(procedura, " ", loadnumber) as "Load Num.",
ddt.status as "Status", date_format(date_add(data_creazione, interval procedure_sprint.scadenza_inventory day), "%d-%m-%Y %H:%i") as "Scad. Inventory" 
from ddt inner join procedure_sprint on ddt.procedura = procedure_sprint.nome_procedura
where ddt.status="Inventory"
order by date_add(data_creazione, interval procedure_sprint.scadenza_inventory day) asc;

What do you mean by saying “the dataset is not allowing that format”?
If you fill a dataset with a sql query and then display it in a label everything is fine, if you want to use the dataset to populate a form you only need to retrieve the PKs of the records and not all the columns so you can adjust the date format using the format property of the field you use to show that column.
Anyway don’t use a dataset to load a form, it is now (servoy 3.1.3) limited to 1000 records, use this instead:

controller.loadRecords(sql_query)
7

Thanks for both robert and nick,

I had come to an end on this issue.I believe that i lead you all on the wrong direction that instead of sybase i mention as mysql.So that i ask sorry to both on this.

8

Hi,

It’s okay. We all get confused sometimes.
So you found that you need to use DATEFORMAT() instead.
Don’t you ‘love’ those slight differences between the main RDBMSs. :evil:

9

Yes Robert the difference between the RDBMS is really a :twisted: ,

Instead of date format() in mysql we need to use the convert() function in sybase.These changes we need to make for the date conversion between both,I think these information will ensure other’s not to confuse in this matter.

10

I guess you can use CONVERT() for formatting dates but that is more a true casting function than a formatting function. If I read the reference pages correctly then you would normally use DATEFORMAT().
Also that function is not much different than what other RDBMSs use other then the slightly different name.

http://www.ianywhere.com/developer/prod … ormat.html
http://www.ianywhere.com/developer/prod … nvert.html

And the format styles used in DATEFORMAT():
http://www.ianywhere.com/developer/prod … ption.html

Hope this helps.