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

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.

Format that I need

How the column looks right now

How column looks right now

Format of RCLTIM - numeric format

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.

CodePudding user response：

What you're attempting to do is very fragile, but I presume your source system gives you few options.

Converting like this has potential problems with DST & the language in use at the time of running.

Your question lost some of the detail regarding formats, so I can't see the time type you are using, but it looks like a decimal again.

Essentially, you need to put the the numerics in a string and then into datetime columns, but to get there you have to match a string conversion format the sqlserver. Fortunately you are not far off the us format default.

Something like this will get you a date field, you can then amend the output format if you really need 12hr rather than 24hr.

SET LANGUAGE us_english
DECLARE @rcldte  NUMERIC, @rcltim numeric
SET @rcldte=20220119
SET @rcltim  = 015250 

SELECT  
CONVERT(DATETIME, CAST (@rcldte AS VARCHAR)  ' '   
LEFT( left('000000', 6-LEN(CAST ( @rcltim AS VARCHAR))) CAST ( @rcltim AS VARCHAR),2) 
  ':'   substring( left('000000', 6-LEN(CAST ( @rcltim AS VARCHAR))) CAST ( @rcltim AS VARCHAR),3,2) 
  ':'   RIGHT( left('000000', 6-LEN(CAST ( @rcltim AS VARCHAR))) CAST ( @rcltim AS VARCHAR),2))

Which will give you:

(No column name)
2022-01-19 01:52:50.000

It's rather ugly though. If you can guarantee the hours are zero padded then you could remove the complexity associated with that. And if you're really going to use it then split into proper UDFs...

CodePudding user response：

DECLARE @RCLDTE CHAR(8) = '20220119';
DECLARE @RCLTIM CHAR(6) = '215250';

select CONVERT(VARCHAR(21), (cast(@RCLDTE as DATETIME)   cast(substring(@RCLTIM,1,2) ':' substring(@RCLTIM,3,2) ':' substring(@RCLTIM,5,2) as DATEtime) ), 22);

output: 01/19/22 9:52:60 PM

For different formats of the DATETIME, see the docs of the CONVERT function:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

NOTE: When specifying another format the value 21 in VARCHAR(21) might need a change...

CodePudding user response：

The number for the date can be casted to a DATE or DATETIME.

And the number for the time can be stuffed with colons before casting it to a TIME or DATETIME.

And two DATETIME can be added to form one.

And FORMAT can be used to get the DATETIME in the specific format you want.
(If CONVERT doesn't have it)

Test snippet

declare @test table (
 ID INT IDENTITY PRIMARY KEY, 
 RCLDTE int, 
 RCLTIM int
);

insert into @test (RCLDTE, RCLTIM) values
  (20220119, 215250)
, (20220304, 070809)
;

select 
  FORMAT(TRY_CAST(LEFT(c.RCLDTE,8) AS DATETIME)
         TRY_CAST(STUFF(STUFF(FORMAT(MAX(c.RCLTIM), '000000'),5,0,':'),3,0,':') AS DATETIME) 
  , 'd/M/yyyy HH:mm:ss tt') AS [Date & Time] 
from @test c
group by RCLDTE;

Date & Time

19/1/2022 21:52:50 PM

4/3/2022 07:08:09 AM

Date & Time
19/1/2022 21:52:50 PM
4/3/2022 07:08:09 AM

Test on db<>fiddle here