I am trying to create a Pivot table in Excel, using Microsoft Query linking to RMS SQL database. SQL stores the date as Date/Time. It becomes impossible to group in Excel using Pivot Table, by date/time. I want to create a record in Microsoft query using the date field, but convert to Date only as dd/mm/yyyy and retain the format as Date and not as Text. I have tried Left(Date,11) and Convert(VARCHAR,1,101), none of them work. Could someone help me with the correct syntax. Microsoft query does not recognize MID.
Thanks