• Registered: 2016-07-28
  • Posts: 1,038

Topic: Date format on SQL Query

I have a button to populate tGrid via SQL Query (Populating tGrid via search wasn't satisfactory so SQL Query used instead).


The following script is written by EHW. I only added date bit.

Select 'PI-' || Invoice.Counter3,'Db-' || Refund.Counter4,Refund.DBCRvalue,
    Refund.PIRdate,Supplier.name,Invoice.product,
    TaxRates.TaxRate,printf('%.2f',Invoice.PIprice),printf('%.2f',Refund.PIRprice),
    Refund.Id
 From Refund 
 Left Outer Join Invoice On Invoice.Id = Refund.id_Invoice 
 Left Outer Join Supplier On Supplier.Id = Refund.id_Supplier
 Left Outer Join TaxRates On TaxRates.Id = (Select TaxRates.Id From TaxRAtes
     Where TaxRates.Id = Invoice.id_TaxRates)
 Where Refund.DBCRValue = 'Db'

All good but I couldn't get date displayed correctly. "PIR Date" field (Date field to be displayed on tGrid under "Date" column, set as Date only not including time. I guess when SQL used it gets row data which includes date and time ( I remember Dmitry saying something like this about display format of values of currency fields on tGrid when calc fields used etc.)
https://s26.postimg.org/690h3cbjd/zzzzz_Temp51.png


How can I get date on tGrid displayed without the time while using SQL script above in a same format as in my PC date time settings?
On the image above "PIR Date" field date format (DD-MM-YYYY) matches to my PC date and time settings, but tGrid date is not .

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2014-02-14
  • Posts: 10,294

Re: Date format on SQL Query

You should use function strftime to format date field, example:

strftime('%d/%m/%Y', Refund.PIRdate)
Dmitry.
  • Registered: 2016-07-28
  • Posts: 1,038

Re: Date format on SQL Query

Thanks a lot Dmitry..............................


I added it in button's SQL Query as follows but it didn't work?

Select 'PI-' || Invoice.Counter3,'Db-' || Refund.Counter4,Refund.DBCRvalue,
    Refund.PIRdate,Supplier.name,Invoice.product,
    strftime('%d/%m/%Y', Refund.PIRdate),
    TaxRates.TaxRate,printf('%.2f',Invoice.PIprice),printf('%.2f',Refund.PIRprice),
    Refund.Id
 From Refund 
 Left Outer Join Invoice On Invoice.Id = Refund.id_Invoice 
 Left Outer Join Supplier On Supplier.Id = Refund.id_Supplier
 Left Outer Join TaxRates On TaxRates.Id = (Select TaxRates.Id From TaxRAtes
     Where TaxRates.Id = Invoice.id_TaxRates)
 Where Refund.DBCRValue = 'Db'
Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2014-02-14
  • Posts: 10,294

Re: Date format on SQL Query

Check it out

Select 'PI-' || Invoice.Counter3,'Db-' || Refund.Counter4,Refund.DBCRvalue,
    strftime('%d/%m/%Y', Refund.PIRdate),Supplier.name,Invoice.product,
    TaxRates.TaxRate,printf('%.2f',Invoice.PIprice),printf('%.2f',Refund.PIRprice),
    Refund.Id
 From Refund 
 Left Outer Join Invoice On Invoice.Id = Refund.id_Invoice 
 Left Outer Join Supplier On Supplier.Id = Refund.id_Supplier
 Left Outer Join TaxRates On TaxRates.Id = (Select TaxRates.Id From TaxRAtes
     Where TaxRates.Id = Invoice.id_TaxRates)
 Where Refund.DBCRValue = 'Db'
Dmitry.
  • Registered: 2016-07-28
  • Posts: 1,038

Re: Date format on SQL Query

I thought, I tried that... Looks like I missed something.


Thank you very much Dmitry..........................
Truly appreciated.........................

Adam
God... please help me become the person my dog thinks I am.