March 3, 2023 at 6:42 am
Hi Forumers,
I have a file with 4 Columns pertaining to Date & Timestamp Values.
I want to join them to show the 4 Columns as one Column with forward slashes between the Year/Month/Day and then a Colon between the Year/Month/Day & the Timestamp field.
An example is attached, thanks for your help.
March 3, 2023 at 6:52 am
DATEFROMPARTS?
March 3, 2023 at 7:37 am
I don't understand your reply?
March 3, 2023 at 7:43 am
I have not opened that attachment. Rather than 'JOIN' it sounds more like you want to concatenate.
Look up the CONCAT() function. It will help you with this.
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.
March 3, 2023 at 11:06 am
Where do you want to do the process? In Excel or in SQL Server?
It is the tradition on this forum that the poster of the question supplies the data in a consumable format. So if you want it done in SQL Server you should include a script to create the table and populate it with test values. e.g.:
DROP TABLE IF EXISTS #MyTable
;
CREATE TABLE #MyTable (
date_fld VARCHAR(2),
month_fld VARCHAR(3),
year_fld VARCHAR(4),
time_fld VARCHAR(8),
Desired_Result VARCHAR(20)
)
;
INSERT INTO #MyTable (date_fld, month_fld, year_fld, time_fld, Desired_Result)
VALUES
(19, 'Jan', 2023, '07:13:51', '19/Jan/2023:7:13:51'),
(23, 'Feb', 2023, '02:37:52', '23/Feb/2023:2:37:52'),
(2, 'Mar', 2022, '10:51:55', '2/Mar/2022:10:51:55'),
(22, 'Apr', 2022, '02:05:60', '22/Apr/2022:2:5:60'),
(18, 'May', 2023, '03:12:60', '18/May/2023:3:12:60'),
(2, 'Jun', 2022, '09:29:18', '2/Jun/2022:9:29:18'),
(18, 'Jul', 2021, '12:40:56', '18/Jul/2021:12:40:56'),
(22, 'Aug', 2022, '03:34:01', '22/Aug/2022:3:34:1'),
(9, 'Sep', 2020, '01:33:60', '9/Sep/2020:1:33:60'),
(7, 'Oct', 2021, '08:44:57', '7/Oct/2021:8:44:57'),
(11, 'Nov', 2022, '11:49:54', '11/Nov/2022:11:49:54'),
(5, 'Dec', 2023, '11:08:08', '5/Dec/2023:11:8:8'),
(13, 'Jan', 2021, '11:23:12', '13/Jan/2021:11:23:12'),
(28, 'Feb', 2023, '12:39:21', '28/Feb/2023:12:39:21'),
(1, 'Mar', 2023, '10:10:42', '1/Mar/2023:10:10:42'),
(21, 'Apr', 2020, '12:48:01', '21/Apr/2020:12:48:1'),
(10, 'May', 2021, '03:35:32', '10/May/2021:3:35:32'),
(9, 'Jun', 2023, '01:41:58', '9/Jun/2023:1:41:58'),
(3, 'Jul', 2021, '12:04:35', '3/Jul/2021:12:4:35'),
(7, 'Aug', 2022, '04:30:40', '7/Aug/2022:4:30:40')
;
Query:
SELECT *,
CONCAT(date_fld, '/', month_fld, '/', year_fld, ':', time_fld) result
FROM #MyTable
;
March 3, 2023 at 5:24 pm
I would use ' ' (space) between the date and time value. I would then cast the result as a 'datetime' data type.
Also, some of the test data has invalid time values. It has times with '60' seconds. Those should be '00' seconds and one extra minute.
That is: '2:5:60' should be '2:6:00'
My solution:
SELECT date_fld
, month_fld
, year_fld
, time_fld
, result = cast(CONCAT(date_fld, '/', month_fld, '/', year_fld, ' ', time_fld) as datetime)
FROM #MyTable
date_fld month_fld year_fld time_fld result
-------- --------- -------- -------- -----------------------
19 Jan 2023 07:13:51 2023-01-19 07:13:51.000
23 Feb 2023 02:37:52 2023-02-23 02:37:52.000
2 Mar 2022 10:51:55 2022-03-02 10:51:55.000
*****
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply