October 19, 2017 at 10:23 am
Jeff,
True enough. Reports and third party software are probably the most common situations where you have to deal with formatting in the database.
Even in those cases it may be feasible to organise the SQL code such that you only need to format once your data manipulation is completed and you have selected a much smaller set of data which should help to mitigate the performance issue.
Your example shows well how an alternative approach can perform better although I would argue that the FORMAT option is clearer in its intent than the better performing CONVERT option which makes it easier to support, particularly where the support team may be less knowledgeable.
I would probably stick to using FORMAT where the number of dates requiring formatting is small e.g. 1 date on each of half a dozen rows but where there are large numbers e.g. 200 page report (does anyone actually read those?) with multiple dates on every row or something that gets run very frequently then performance matters and your CONVERT alternative would clearly be better. For cases between these examples which would be better will depend on the specific circumstances.
October 19, 2017 at 11:48 am
The OP is using SSMS as a reporting tool. Just look at some of the other threads on the same topic.
October 19, 2017 at 5:01 pm
crmitchell - Thursday, October 19, 2017 10:23 AMJeff,True enough. Reports and third party software are probably the most common situations where you have to deal with formatting in the database.
Even in those cases it may be feasible to organise the SQL code such that you only need to format once your data manipulation is completed and you have selected a much smaller set of data which should help to mitigate the performance issue.Your example shows well how an alternative approach can perform better although I would argue that the FORMAT option is clearer in its intent than the better performing CONVERT option which makes it easier to support, particularly where the support team may be less knowledgeable.
I would probably stick to using FORMAT where the number of dates requiring formatting is small e.g. 1 date on each of half a dozen rows but where there are large numbers e.g. 200 page report (does anyone actually read those?) with multiple dates on every row or something that gets run very frequently then performance matters and your CONVERT alternative would clearly be better. For cases between these examples which would be better will depend on the specific circumstances.
You're absolutely correct when you say...
...organise the SQL code such that you only need to format once your data manipulation is completed and you have selected a much smaller set of data which should help to mitigate the performance issue.
When I teach T-SQL methods that do require some formatting for the output, I tell folks that they still need to keep the Data and Presentation layers separate, especially since formatting can be such a load on the code. Just like you say above, do the heavy lifting and aggregations first, which also usually greatly reduces the amount of data that has to go through any type of formatting including even something as simple as pivoting the data. Although both a CROSS TAB or the PIVOT operator requires aggregation, it's a rather expensive aggregation because of the decision required to determine which column things must go into. With that, I do what Peter "PESO" Larsson calls "pre-aggregation", which first aggregates the data to the smaller result set and then do the CROSS TAB or PIVOT on that greatly reduced data.
On the continued used of FORMAT... I'll never use it so long as it has such terrible performance even if the code is more clear. My feeling on all of that can be summed up by two points...
1. I won't use something that's got a built in performance problem because performance and resource usage are second only to accuracy and it's a very close second.
2. If someone doesn't know how to use CONVERT, they should do a bit of studying. It's a part of their job to do so. It's like saying that I shouldn't use a Tally Table (or equivalent function) and should use While Loops instead simply because someone might not understand what it does. A well placed comment in the code can also help there, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 10:35 am
chef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
Chris
In a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 20, 2017 at 10:50 am
jcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 11:01 am
Jeff Moden - Friday, October 20, 2017 10:50 AMjcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.
Even more likely when you consider that FORMAT() was introduced on 2012.
October 20, 2017 at 11:03 am
jcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
GET OFF THE BACKS OF COBOL PROGRAMMERS!
I am sick of your bashing.
October 20, 2017 at 12:07 pm
Jeff Moden - Friday, October 20, 2017 10:50 AMjcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.
>>I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.<<
Actually, it was. The only model we had for programming commercially was COBOL, which is based on string handling. Temporal data types are based basically on a PDP 11 OS under UNIX. That's why we have that funny count of clock ticks and various UNIX temporal display formats.
Look at the money datatype. All of its commas, periods, currency symbols, etc. are based on the COBOL picture clause. Back then we did not even have the concept of a presentation layer, so following the COBOL model, we built it into the data type itself.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 20, 2017 at 12:10 pm
jcelko212 32090 - Friday, October 20, 2017 12:07 PMJeff Moden - Friday, October 20, 2017 10:50 AMjcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.
>>I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.<<
Actually, it was. The only model we had for programming commercially was COBOL, which is based on string handling. Temporal data types are based basically on a PDP 11 OS under UNIX. That's why we have that funny count of clock ticks and various UNIX temporal display formats.
Look at the money datatype. All of its commas, periods, currency symbols, etc. are based on the COBOL picture clause. Back then we did not even have the concept of a presentation layer, so following the COBOL model, we built it into the data type itself.
Oh, so YOU ARE TO BLAME. How about bashing yourself from now on.
October 20, 2017 at 3:46 pm
jcelko212 32090 - Friday, October 20, 2017 12:07 PMJeff Moden - Friday, October 20, 2017 10:50 AMjcelko212 32090 - Friday, October 20, 2017 10:35 AMchef423 - Wednesday, October 11, 2017 1:03 PMSelect Invoice_Exceptions.Invoice_Number, Invoice_Exceptions.ItemNum, Inventory.ItemName, Invoice_Exceptions.Amount, Invoice_Exceptions.Quantity,
Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName, Invoice_Exceptions.DateTime AS Day_Voided, Invoice_Totals.Payment_Method
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 04:14:50.000' and '2017-10-11 02:14:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASCInvoice_Exceptions.DateTime is a 'datetime' data type and is displaying as '2017-10-01 10:57:53.000'
I'd love for the 'datetime' to display: 10-01-2017 10:57 PM
I cant seem to find the right CAST for FORMAT, format...I seem to be missing something.
Any help appreciated,
Best,
ChrisIn a modern tiered architecture, like SQL, display formatting is done in a presentation layer. The database layer gets the data, and returns it in a standardized format to other layers. In the case of ANSI/ISO standard SQL, we use an ISO 8601 display format, which is "yyyy-mm-dd HH:MM:ssss.." And it's the only game in town anything else is a local dialect or left over from the original Sybase days. The reason things like format or convert exist is in the early days of SQL, most of the programmers knew COBOL. This language treats everything as strings, and has a thing called a PICTURE clause as part of the data declaration. Think the regular expression template that adds punctuation and other formatting to strings. SQL works at a higher, more abstract level with its data.
I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.
>>I seriously doubt that Sybase included CONVERT to format things just to appease COBOL users. More likely, it was to appease anyone that may simply need a different format when there is no presentation layer available.<<
Actually, it was. The only model we had for programming commercially was COBOL, which is based on string handling. Temporal data types are based basically on a PDP 11 OS under UNIX. That's why we have that funny count of clock ticks and various UNIX temporal display formats.
Look at the money datatype. All of its commas, periods, currency symbols, etc. are based on the COBOL picture clause. Back then we did not even have the concept of a presentation layer, so following the COBOL model, we built it into the data type itself.
Interesting.... thanks for the history lesson, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply