Convert Date Function – R_SB_09 Report in PCM

A friend reached out to me this week with a new wrinkle in report R_SB_09 where the date format in the received column was wrong. Instead of 10/28/2012 appearing it was showing up as 28/10/2012. My first inkling was to look at the presentation or layout side of the report and look at date format. Much to my surprise, there wasn’t any formatting. Hmm, must be an issue with the data model? Sure enough, when I edited the data model and looked at data, received was 28/10/2012 and sent right next to it was 10/28/2012. Suspicion confirmed! The data model contained the following: CONVERT(nvarchar(30),SBMT.LATEST_RECVD_DATE,103) as LATEST_RECVD_DATE, CONVERT(nvarchar(30),SBMT.LATEST_SENT_DATE,101) as LATEST_SENT_DATE, First thing I noticed was converting the date being part of the syntax rather than just pulling the date and the one I liked (Sent) had a 101 and the other (Received) had a 103 in the variables. Simple solution! Change the 103 to 101. I did and it worked but why? Turns out 103 is British formatting and 101 is English, but there are many more choices available. Google “Oracle SQL Convert 101” to learn more.