October 11, 2017 at 1:03 pm
Select 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 ASC
Invoice_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
October 11, 2017 at 1:23 pm
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
Try this
DECLARE @SomeDate DATETIME = '2017-10-01 10:57:53.000';
SELECT
@SomeDate
, FORMAT(@SomeDate, 'MM-dd-yyyy hh:mm tt');
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2017 at 1:25 pm
Note also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2017 at 1:32 pm
Phil Parkin - Wednesday, October 11, 2017 1:23 PMchef423 - 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,
ChrisTry this
DECLARE @SomeDate DATETIME = '2017-10-01 10:57:53.000';SELECT
@SomeDate
, FORMAT(@SomeDate, 'MM-dd-yyyy hh:mm tt');
Thanks Phil, that looks perfect, now to fit it into my code correctly....
October 11, 2017 at 1:34 pm
FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy hh:mm tt')
Wow, I feel like a numskull, I swear I tried that....thanks again.
October 11, 2017 at 1:50 pm
Phil Parkin - Wednesday, October 11, 2017 1:25 PMNote also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.
I'm trying to convert the 'Invoice_Exceptions.Amount' that displays as '2.00000000'
Id like for it to display as: $2.00
I am trying this code:'$'+CONVERT(decimal(25, 8), CAST(Invoice_Exceptions.Amount as money), -1)
But I get an error, the dataType is decimal(25, 8)
October 11, 2017 at 2:08 pm
chef423 - Wednesday, October 11, 2017 1:50 PMPhil Parkin - Wednesday, October 11, 2017 1:25 PMNote also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.I'm trying to convert the 'Invoice_Exceptions.Amount' that displays as '2.00000000'
Id like for it to display as: $2.00
I am trying this code:'$'+CONVERT(decimal(25, 8), CAST(Invoice_Exceptions.Amount as money), -1)But I get an error, the dataType is decimal(25, 8)
You need to CONVERT to a string data type to give format.SELECT '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money))
FROM (SELECT 2 AS Amount) Invoice_Exceptions
Why are you not formatting everything in the front-end?
October 11, 2017 at 2:08 pm
chef423 - Wednesday, October 11, 2017 1:50 PMPhil Parkin - Wednesday, October 11, 2017 1:25 PMNote also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.I'm trying to convert the 'Invoice_Exceptions.Amount' that displays as '2.00000000'
Id like for it to display as: $2.00
I am trying this code:'$'+CONVERT(decimal(25, 8), CAST(Invoice_Exceptions.Amount as money), -1)But I get an error, the dataType is decimal(25, 8)
Here is one way. There are probably more-elegant solutions to this:
SET NUMERIC_ROUNDABORT OFF;
DECLARE @SomeText VARCHAR(20) = '2.00000000';
SELECT
@SomeText
, CONCAT('$', CAST(CAST(@SomeText AS DECIMAL(19, 2)) AS VARCHAR(20)));
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2017 at 2:17 pm
Luis, thanks! Phil, you as well....I appreciate you both.
October 11, 2017 at 2:18 pm
chef423 - Wednesday, October 11, 2017 2:17 PMLuis, thanks! Phil, you as well....I appreciate you both.
FINISHED CODE:
Select Invoice_Exceptions.Invoice_Number, FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy') as Date_of_Void, Invoice_Exceptions.ItemNum, Inventory.ItemName, '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money)) as Amount$,
CONVERT(varchar(10), CAST(Invoice_Exceptions.Quantity as INT)) as Quanity, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName as Employee, FORMAT(Invoice_Exceptions.DateTime, 'hh:mm tt') as Time_Voided, Invoice_Totals.Payment_Method as Cash_Void
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 ASC
October 11, 2017 at 2:24 pm
Phil Parkin - Wednesday, October 11, 2017 1:25 PMNote also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.
To ask, what is a more robust option?
Thank you.
October 11, 2017 at 9:54 pm
chef423 - Wednesday, October 11, 2017 1:34 PMFORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy hh:mm tt')Wow, I feel like a numskull, I swear I tried that....thanks again.
Like Phil said, FORMAT is notorious for performance issues. For all its wonderful simplicity, I won't use it on a bet except to bet against it. Run the following code and see that FORMAT is 14.25 times slower even when you have to use 2 CONVERTs and a STUFF to do the same thing.
--===== Define the date range.
DECLARE @StartDate DATETIME = '2000' --Inclusive
,@LimitDate DATETIME = '2017' --Exclusive
,@Rows INT = 1000000
;
DECLARE @Days INT = DATEDIFF(dd,@StartDate,@LimitDate)
;
--===== Create the test table
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Populate the table with
SELECT TOP 1000000
SomeDate = RAND(CHECKSUM(NEWID()))*@Days+@StartDate
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Test the format method with a shunt variable to take the display time out of the picture.
PRINT '========== FORMAT Method =======================================================';
GO
DECLARE @Bitbucket CHAR(19);
SET STATISTICS TIME ON;
SELECT @Bitbucket = FORMAT(SomeDate, 'MM-dd-yyyy hh:mm tt')
FROM #TestTable;
SET STATISTICS TIME OFF;
GO 3
--===== Test the convert method with a shunt variable to take the display time out of the picture.
PRINT '========== CONVERT Method =======================================================';
GO
DECLARE @Bitbucket CHAR(19);
SET STATISTICS TIME ON;
SELECT @Bitbucket = CONVERT(CHAR(11),SomeDate,110)+STUFF(RIGHT(CONVERT(CHAR(19),SomeDate,100),7),6,0,' ')
FROM #TestTable;
SET STATISTICS TIME OFF;
GO 3
Supposedly, it uses the .identically name net FORMAT function behind the scenes. I don't know for sure but it must use some horrible form of RegEx to be so slow.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2017 at 9:56 pm
chef423 - Wednesday, October 11, 2017 2:24 PMPhil Parkin - Wednesday, October 11, 2017 1:25 PMNote also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.To ask, what is a more robust option?
Thank you.
See above.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2017 at 3:21 am
chef423 - Wednesday, October 11, 2017 2:24 PMTo ask, what is a more robust option?Thank you.
As Luis intimated earlier in the thread.
Do your formatting in the Presentation rather than the data layer i.e. in the GUI rather than in the DB
That way you will only need to format once and only for those rows required but mostly because if you need to display in a different format then you only need to update the GUI and not all layers of the application.
October 19, 2017 at 8:18 am
crmitchell - Thursday, October 19, 2017 3:21 AMchef423 - Wednesday, October 11, 2017 2:24 PMTo ask, what is a more robust option?Thank you.
As Luis intimated earlier in the thread.
Do your formatting in the Presentation rather than the data layer i.e. in the GUI rather than in the DBThat way you will only need to format once and only for those rows required but mostly because if you need to display in a different format then you only need to update the GUI and not all layers of the application.
While I agree that you should always try to keep the Presentation Layer separate from the Data Layer (especially for "auto-magically" localizing the format of dates and times), you still need to know how to do it all in the Data Layer because there are times when there is no separate Presentation Layer because there is no interplay with a GUI or any other external presentation functionality or, if there is, it's so horribly slow that no one wants to use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply