Having trouble displaying a date in a certain format...

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 1:03 pm

  #352115

  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

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  October 11, 2017 at 1:23 pm

  #1963250

  chef423 - Wednesday, October 11, 2017 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

  Try this

  DECLARE @SomeDate DATETIME = '2017-10-01 10:57:53.000';

  SELECT
  @SomeDate
, FORMAT(@SomeDate, 'MM-dd-yyyy hh:mm tt');

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  October 11, 2017 at 1:25 pm

  #1963251

  Note also that the FORMAT() function is renowned for being a slow performer, so I don't recommend this for large record sets.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 1:32 pm

  #1963255

  Phil Parkin - Wednesday, October 11, 2017 1:23 PM

  chef423 - Wednesday, October 11, 2017 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

  Try 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....

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 1:34 pm

  #1963257

  FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy hh:mm tt')

  Wow, I feel like a numskull, I swear I tried that....thanks again.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 1:50 pm

  #1963258

  Phil Parkin - Wednesday, October 11, 2017 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.

  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)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 11, 2017 at 2:08 pm

  #1963261

  chef423 - Wednesday, October 11, 2017 1:50 PM

  Phil Parkin - Wednesday, October 11, 2017 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.

  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?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  October 11, 2017 at 2:08 pm

  #1963262

  chef423 - Wednesday, October 11, 2017 1:50 PM

  Phil Parkin - Wednesday, October 11, 2017 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.

  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)));

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 2:17 pm

  #1963265

  Luis, thanks! Phil, you as well....I appreciate you both.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 2:18 pm

  #1963266

  chef423 - Wednesday, October 11, 2017 2:17 PM

  Luis, 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


• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 11, 2017 at 2:24 pm

  #1963268

  Phil Parkin - Wednesday, October 11, 2017 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.

  To ask, what is a more robust option?

  Thank you.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 11, 2017 at 9:54 pm

  #1963302

  chef423 - Wednesday, October 11, 2017 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.

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 11, 2017 at 9:56 pm

  #1963303

  chef423 - Wednesday, October 11, 2017 2:24 PM

  Phil Parkin - Wednesday, October 11, 2017 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.

  To ask, what is a more robust option?

  Thank you.

  See above.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• crmitchell

  SSCarpal Tunnel

  Points: 4557

  More actions

  October 19, 2017 at 3:21 am

  #1964322

  chef423 - Wednesday, October 11, 2017 2:24 PM

  To 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.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 19, 2017 at 8:18 am

  #1964349

  crmitchell - Thursday, October 19, 2017 3:21 AM

  chef423 - Wednesday, October 11, 2017 2:24 PM

  To 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.

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)