Change the numeric format into 'AM/PM' format in CONCAT / MAX (sql)
-
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.
-
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. -
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 - 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 DateTimeStringMy 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) - 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) - 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.
-
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
;
GOHere'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://www.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
--==============================================================================
GOHere 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) -
Hello everyone,
When running on a big dataset I start getting an error "Conversion failed when converting date and/or time from character string."
-
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 nullAny rows that are null did not convert to date and/or time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply