Change the numeric format into 'AM/PM' format in CONCAT / MAX (sql)

Question

Post reply

Change the numeric format into 'AM/PM' format in CONCAT / MAX (sql)

JeremyU

SSC-Addicted

Points: 449
More actions
March 17, 2022 at 4:34 pm

#4001368
I need to change the numeric format into 'AM/PM' format in CONCAT function (I use SSMS v 18.5.1)

Here is my formula. RCLDTE - is a date and RCLTIM is time. I basically need to leave RCLDTE as it is and change the format of RCLTIM from numeric to date and convert to AM/PM format.

How the column looks right now

Format of RCLTIM - numeric
```
CONCAT(c.RCLDTE, ' & ', MAX(c.RCLTIM)) AS 'Date & Time',
```
When I tried to use CONVERT function as I tend to use, it raised an error.
```
CONCAT(c.RCLDTE, ' & ', CONVERT(varchar(15),CAST( MAX(c.RCLTIM) AS TIME),100))
```
Error
```
Explicit conversion from data type numeric to time is not allowed.
```

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 1

My opinion - this should be solved at the presentation layer, but you also have some data issues. First, I'd fix the data issues - your RCLTIM column is a numeric data type (int?) which cannot be cast to TIME. And even if it was successful, the format you have for time as an integer is NOT likely to be what SQL (or any tool) would be expecting. So first, you need to convert the RCLTIM column into a format that can be converted to a time. I would recommend doing it to a VARCHAR, but you will need to be careful converting it depending on how you handle hours like "01". What I expect, since the column is numeric, is that 1 AM would be stored as 10000, and 10 AM would be 100000 (1 extra 0 on the end). If that is the case, then it is just a simple matter of slapping some :'s in to it so it can be converted to a time and then you should be able to do your CAST CONVERT method you suggested.

Now, why I would do it at the presentation layer - that's what it is for. SQL shouldn't be used to format your data - it's extra strain on the server - not a lot, but if you have millions of requests to this per second and have millions of rows, that's going to eat up a lot of server resources that could be better used. At the application layer, that eats up resources on the client machine. Client machines are MUCH cheaper to give more resources to than server resources.

I'd ALSO change your RCLDTE column to a DATE data type.

Now, the above being said, if you inherited this system OR the data comes from a 3rd party tool, I wouldn't change anything - there MAY be a lot of things that rely on those dates and times being numeric and changing it may result in massive code changes to make things play nice. BUT if this is something you developed OR is only used by a small number of apps that are easy to change, I would HIGHLY recommend using appropriate data types.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Ed B Hall of Fame Points: 3130 More actions · Answer 2

If you store times as integers, it makes life much easier if you have a time table containing all the formats you need, however, if you need to just change how the values appear it can be done by adding ':' between timeparts, converting to time, and then converting back to a varchar with a style code. I am not suggesting it is a good idea, but do any of these meet your needs?

DECLARE @RCLDTE INT = 20220119,
        @RCLTIM INT = 215250 --235250

SELECT  LTRIM(CONVERT(VARCHAR(12),CONVERT(TIME(0),STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':')),22)) AS TimeString,
        RIGHT(CONCAT('0', LTRIM(CONVERT(VARCHAR(12),CONVERT(TIME(0),STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':')),22))),11) AS TimeStringPadded,
        CONVERT(CHAR,CONVERT(DATETIME,CONCAT(CONVERT(DATE,CONVERT(VARCHAR(8),@RCLDTE)), ' ', STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':'))),22) AS DateTimeString

This reply was modified 2 years, 6 months ago by Ed B.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Ed B wrote:

If you store times as integers, it makes life much easier if you have a time table containing all the formats you need, however, if you need to just change how the values appear it can be done by adding ':' between timeparts, converting to time, and then converting back to a varchar with a style code. I am not suggesting it is a good idea, but do any of these meet your needs?
DECLARE @RCLDTE INT = 20220119,
        @RCLTIM INT = 215250 --235250

SELECT  LTRIM(CONVERT(VARCHAR(12),CONVERT(TIME(0),STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':')),22)) AS TimeString,
        RIGHT(CONCAT('0', LTRIM(CONVERT(VARCHAR(12),CONVERT(TIME(0),STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':')),22))),11) AS TimeStringPadded,
        CONVERT(CHAR,CONVERT(DATETIME,CONCAT(CONVERT(DATE,CONVERT(VARCHAR(8),@RCLDTE)), ' ', STUFF(STUFF(CONVERT(VARCHAR(10), @RCLTIM),3,0, ':'), 6, 0,':'))),22) AS DateTimeString

My recommendation would be to not store such things and not go through any character data in the conversion of integer dates and times to real dates and times. Integer conversions are MUCH faster. For example...

--===== Conditionally drop the test table just to make reruns in SSMS easier.
   DROP TABLE IF EXISTS #TestTable
;
GO
--===== Create TestTable.
     -- This table has an Index-able PERSISTED computed column to do the date/time translation for DATETIME2(0)
 CREATE TABLE #TestTable 
        (
         IntDate    INT
        ,IntTime    INT
        )
;
--===== Populate the test table with integer dates and times.
 INSERT #TestTable 
        (IntDate,IntTime)
 VALUES  (20210129,193402)
        ,(20181214,142254)
        ,(20181214,254)
        ,(20180101,54)
        ,(20180601,1)
;
--===== A nasty fast solution to convert to the DATETIME data type.
 SELECT  *
        ,ConvertedDateTime = DATETIMEFROMPARTS(IntDate/10000, IntDate/100%100, IntDate%100 --Date
                                              ,IntTime/10000, IntTime/100%100, IntTime%100 --Time
                                              ,0) --Milleseconds
   FROM #TestTable
;

Results:

If you need to do such a thing on a regular basis, then add the formula to the table as a PERSISTED computed column. Then, you can easily CONVERT the DATETIME to anything you'd like. Whatever you do, don't actually store the formatted date. Not only will it take a whole lot more space, it will kill the flexibility of being able to CONVERT to any display format.

And, whatever you do, DO NOT USE THE FORMAT FUNCTION!!! It's stupid slow anywhere from about 28 times slower to 53 times slower in the testing that I'm doing for a presentation I'm giving to my local SQL User Group in April.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

JeremyU wrote:

I need to change the numeric format into 'AM/PM' format in CONCAT function (I use SSMS v 18.5.1)

Here is my formula. RCLDTE - is a date and RCLTIM is time. I basically need to leave RCLDTE as it is and change the format of RCLTIM from numeric to date and convert to AM/PM format.

How the column looks right now

Format of RCLTIM - numeric
CONCAT(c.RCLDTE, ' & ', MAX(c.RCLTIM)) AS 'Date & Time',
When I tried to use CONVERT function as I tend to use, it raised an error.
CONCAT(c.RCLDTE, ' & ', CONVERT(varchar(15),CAST( MAX(c.RCLTIM) AS TIME),100))
Error
Explicit conversion from data type numeric to time is not allowed.

In the future, please post some data in a readily consumable format like I did in my post above. It really helps people help you and usually removes all doubt about what the data actually looks like.

--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)
Intro to Tally Tables and Functions

Ed B Hall of Fame Points: 3130 More actions · Answer 5

Jeff Moden wrote:

--===== A nasty fast solution to convert to the DATETIME data type. SELECT * ,ConvertedDateTime = DATETIMEFROMPARTS(IntDate/10000, IntDate/100%100, IntDate%100 --Date ,IntTime/10000, IntTime/100%100, IntTime%100 --Time ,0) --Milleseconds FROM #TestTable ;

This is great, thank you. Much nicer than clumsy string manipulations.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Just in case there are any doubters, here's the code to build a 10 million row test table... don';t let that scare you. It takes a little over 5 seconds to execute and a bit less than 242MB to store on disk or retrieve to memory.

--==============================================================================
--      Presets
--==============================================================================
--===== Parameters to control the desired range of dates and and number of rows
DECLARE  @LoDateLimit   DATETIME    = '2020'   --Inclusive, same as 2020-01-01
        ,@HiDateLimit   DATETIME    = '2030'   --Exclusive, same as 2030-01-01
        ,@RowCount      INT         = 10000000 --Currently, 10 million rows
;
--===== Local constants to simplify formulas and increase performance.
DECLARE  @DayCount      INT         = DATEDIFF(dd,@LoDateLimit,@HiDateLimit)
;
--===== If the test table already exists, drop it to make reruns in SSMS easier.
     -- Using "old" method for bre-2012 folks.
     IF OBJECT_ID('dbo.TestIntDateTime') IS NOT NULL
   DROP TABLE dbo.TestIntDateTime
;
--==============================================================================
--      Create and populate the table (HEAP) on the fly using given parameters.
--==============================================================================
   WITH CTE AS
(
 SELECT TOP (@RowCount) --Needs to be hardocded for 2005 or less.
        SomeDateTime = RAND(CHECKSUM(NEWID()))*@DayCount+@LoDateLimit
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
 SELECT  SomeDateTime
        ,IntDate    = DATEPART(yy,SomeDateTime)*10000
                    + DATEPART(mm,SomeDateTime)*100
                    + DATEPART(dd,SomeDateTime)
        ,IntTime    = DATEPART(hh,SomeDateTime)*10000
                    + DATEPART(mi,SomeDateTime)*100
                    + DATEPART(ss,SomeDateTime)
   INTO dbo.TestIntDateTime 
   FROM CTE
 OPTION (RECOMPILE) --Force re-evaluation of variables for performance
;
GO

Here's the test code I used to test 4 different methods. DO NOT RUN THIS CODE ON A PROD BOX BECAUSE IT CLEAR PROC AND BUFFER CACHE!!!!

It uses the trick of dumping results to a "throw away" variable to take display time out of the picture. You must not use SET STATISTICS TIME/IO on because one of the sections of code tests an undocumented but frequently used scalar function that has been in tghe MSDB database since at least 2005 and maybe earlier. The statistics make scalar functions perform a whole lot worse than they actually are. Please see the following article for proof of that.

https://qa.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

--==============================================================================
        CHECKPOINT;
   DBCC FREEPROCCACHE    WITH NO_INFOMSGS;
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== Character Based =======================================================
DECLARE @BitBucket DATETIME;
 SELECT @BitBucket = CONVERT(DATETIME
                     , CONVERT(CHAR(9),IntDate)
                     + STUFF(STUFF(RIGHT('000000'
                     + CAST(IntTime AS VARCHAR(12)),6),5,0,':'),3,0,':'))
 FROM dbo.TestIntDateTime
;
GO 3
--==============================================================================
        CHECKPOINT;
   DBCC FREEPROCCACHE    WITH NO_INFOMSGS;
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== msdb.dbo.agent_datetime ================================================
DECLARE @BitBucket DATETIME;
 SELECT @BitBucket = msdb.dbo.agent_datetime(IntDate,IntTime)
   FROM dbo.TestIntDateTime
;
GO 3
--==============================================================================
        CHECKPOINT;
   DBCC FREEPROCCACHE    WITH NO_INFOMSGS;
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== <2012 Integer Math =====================================================
DECLARE @BitBucket DATETIME;
 SELECT @BitBucket = DATEADD(mm,IntDate/10000*12   --Years to months
                             + IntDate/100%100     --Months
                             - 22801,0)            --1900*12+1 months
                   + DATEADD(dd,IntDate%100-1,0)   --Day-1
                   + DATEADD(ss,IntTime/10000*3600 --Hours to seconds
                             + IntTime/100%100*60  --Minutes to seconds
                             + IntTime%100,0)      --Seconds
  FROM dbo.TestIntDateTime
;
GO 3
--==============================================================================
        CHECKPOINT;
   DBCC FREEPROCCACHE    WITH NO_INFOMSGS;
   DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
--===== >=2012 Integer Math ====================================================
DECLARE @BitBucket DATETIME;
 SELECT @BitBucket = DATETIMEFROMPARTS(IntDate/10000,IntDate/100%100,IntDate%100
                                      ,IntTime/10000,IntTime/100%100,IntTime%100
                                      ,0)
   FROM dbo.TestIntDateTime 
;
GO 3
--==============================================================================
GO

Here are the results of the test from SQL Profiler (my old and trusted friend... still haven't gotten used to Extended Events).

--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)
Intro to Tally Tables and Functions

JeremyU SSC-Addicted Points: 449 More actions · Answer 7

JeremyU

SSC-Addicted

Points: 449

March 18, 2022 at 3:22 pm

#4001960

Hello everyone,

When running on a big dataset I start getting an error "Conversion failed when converting date and/or time from character string."

This reply was modified 2 years, 6 months ago by JeremyU.
This reply was modified 2 years, 6 months ago by JeremyU.

ratbak SSC Eights! Points: 939 More actions · Answer 8

However, when running on a big dataset I start getting an error "Conversion failed when converting date and/or time from character string."

Unfortunately it means what it sounds like -- there are RCLDTE or RCLTIM values that do not convert to valid date/time values using the conversions specified. At best, you may have values that can convert to valid date & time, but in different formats other than expected (doesn't seem likely given the expected format, & could be hard to interpret & handle alternatives). More likely, you have values that are not valid dates and/or times. Are RCLDTE or RCLTIM nullable?

You can detect the rows that aren't converting by using try_convert & getting rows where those are null -- e.g., something like

SELECT 
    TRY_CONVERT(date,convert(char(8),c.RCDDTE)) AS date_convert, 
    TRY_CONVERT(varchar,cast(STUFF(STUFF(RIGHT('0000000'+CAST(c.RCLTIM AS varchar(20)),6),5,0,':'),3,0,':') as time),100) AS time_convert
FROM <your tables here>
WHERE TRY_CONVERT(date,convert(char(8),c.RCDDTE)) is null or TRY_CONVERT(varchar,cast(STUFF(STUFF(RIGHT('0000000'+CAST(c.RCLTIM AS varchar(20)),6),5,0,':'),3,0,':') as time),100)is null

Any rows that are null did not convert to date and/or time.