April 4, 2012 at 5:00 am
Hi,
Sorry i am really new to SQL and teaching myself with a book.
I have written this basic query:
SELECT MAX(Timesheet_Date) AS Last_Timesheet_Date, MIN(Timesheet_Date) AS First_Timesheet_Date, Personnel_Ref, Client_Ref
FROM Valid_Timesheets
GROUP BY Personnel_Ref, Client_Ref
HAVING (Client_Ref LIKE 'SWAR34%')
The date is puts out for the date for example is '20110710'
I want it do read 10072011 (10th July 2011)
Do you know how I could change the order of the numbers when Im using the max and min function on the same section?
Many thanks for any help.
April 4, 2012 at 5:03 am
wrap the dates in a convert/cast function using a format code
eg
CONVERT(DATE,MAX(DATE),103)
CAST(MAX(DATE),DATE,103)
April 4, 2012 at 5:12 am
I strongly recommend that you change the Timesheet_Date column to datetime if it is in your power to do so. This will save space in your database and, more importantly, make it much easier to do comparisons. Obviously you will want to test that doing so doesn't break anything before you do it on a live server.
John
April 4, 2012 at 5:55 am
Thanks very much this has worked:
SELECT CAST(MAX(Timesheet_Date) AS Datetime) AS LastTimesheetDate, CAST(MIN(Timesheet_Date) AS dATETIME) AS First_Timesheet_Date, Personnel_Ref,
Client_Ref
FROM Valid_Timesheets
GROUP BY Personnel_Ref, Client_Ref
HAVING (Client_Ref LIKE 'SWAR34%')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply