MySQL Forums
Forum List  »  Newbie

Date Format question
Posted by: Tim Lawson
Date: June 07, 2022 01:53PM

Greetings. I am very much a beginner. Can someone tell me what needs to be done to add the number of a month in front of the month name? For example, I want January to output as "01 Jan", February as "02 Feb", March as "03 Mar", etc. My current code, which is just outputting the month name is as follows:





SELECT a.[Site]

,a.[Department]

,a.[Line]

,a.[LineType]

,a.[ProductionMonth]

,SUM([GoodParts]) as GoodParts

,SUM([TotalParts]) as TotalParts

,SUM([ScrapParts]) as ScrapParts

,SUM([AvailSec]) as AvailSec

,SUM([RunSec]) as RunSec

,SUM([DownSec]) as DownSec

,SUM([TotalSec]) as TotalSec

,SUM([AvailableQty]) as AvailableQty

,SUM([BleedQty]) as BleedQty

,SUM([BlockedQty]) as BlockedQty

,SUM([ProducedQty]) as ProducedQty

,SUM([ReworkQty]) as ReworkQty

,SUM([TotalTime]) as TotalTime

,SUM([IdealCycleTime]) as IdealCycleTime

,CASE WHEN SUM(AvailSec) = 0 THEN 0 ELSE (SUM(RunSec) / SUM(AvailSec)) * 100 END AS Availability

,CASE WHEN SUM(RunSec) = 0 THEN 0 ELSE ((SUM(TotalParts * IdealCycleTime)) / SUM(RunSec)) * 100 END AS Performance

,CASE WHEN SUM(TotalParts) = 0 THEN 1 ELSE (SUM(GoodParts) / SUM(TotalParts)) * 100 END AS Quality

,CASE WHEN SUM(AvailSec) = 0 THEN 0

WHEN SUM(GoodParts+[BlockedQty]+[ReworkQty]) = 0 THEN ((SUM(TotalParts * IdealCycleTime) / SUM(AvailSec))) * 100 ELSE ((SUM(GoodParts * IdealCycleTime) / SUM(AvailSec))) * 100 END AS OEE

FROM (

SELECT *, FORMAT(ProductionDayHour,'MMMM') as ProductionMonth

FROM [dataview].[dbo].[RA_PerformanceEvents]

where DATEDIFF(month,ProductionDayHour,getdate())<9) a

GROUP BY a.[Site], a.[Department], a.[Line], a.[LineType], a.[ProductionMonth]

Options: ReplyQuote


Subject
Written By
Posted
Date Format question
June 07, 2022 01:53PM
June 07, 2022 02:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.