How to format Google fusion Table Datetime Column in Query(dd-MM-yyyy)

[Selva Kumar]

I Need Sample Query for format Datetime into (dd-MM-yyyy) format..Please Help me

[TimAI2]

Have you done the Pizza Party tutorial ?

http://appinventor.mit.edu/explore/ai2/pizzaparty.html

See the section on "Inserting data into the Fusion Table"

[Selva Kumar]

Thanks for Your Reply.I want to format Date time Column in Select Query

[Abraham Getzler]

From the Fusion Tables SQL Reference page ...

Filtering on DATETIME

When filtering on a column of type DATETIME, the <value> should be formatted as one of the following supported formats:

• MMM dd, yy
• MM/dd/yy
• MM-dd-yy
• MMM-dd-yy
• yyyy.MM.dd
• dd-MMM-yy
• MMM/yy
• MMM yy
• dd/MMM/yy
• yyyy

 Your format of dd-MM-yyyy is not on the list.

If you use the text JOIN block together with Clock blocks,

you can build one of the accepted formats for your query.

ABG

[Selva Kumar]

Thanks for your Quick reply,,

ok.i agree your Answer.My table Contains

Orderdate

04/24/2018

04/27/2018

05/01/2018

Now i need to format this Column..for example My Requirement is need only Year part from this Column

then Where will you apply this format condition in Query ?

In Mysql: 

we Use

Select Date_format(orderdate  , '%yyyy') from TableName

similar like , i need Sql Query for fusion table

[Abraham Getzler]

A close reading of https://developers.google.com/fusiontables/docs/v2/sql-reference

resulted in very little in the way of date formatting functions on the Fusion Tables platform.

(You get what you pay for.)

However, if you get the dates in the form MM/DD/YYYY you can use the AI2

text SEGMENT and JOIN blocks to extract and recombine the parts you want

after the results arrive from Fusion Tables.

So YYYY would be SEgMENT('MM/DD/YYYY',from position 7,for a length of 4) 

(I am working from memory, double check the segment block tool tip to be sure.)

If you want to SELECT only a particular year 2016 from Fusion Tables,

code a SELECT ... from <tableID> where MyDate >= 1/1/2016 AND MyDate <= 12/31/2016

(give or take an apostrophe or two,  see what works.) 

ABG