Table 1 has a date column in the following format:
2021-05-01 03:00:00
Table 2 has a date columns in this format:
2021-05-01 03:00.0000000
(edited from 2021-05-01 03:00:00)
How can I show the date in Table 2 to be like the date in Table 1?
I search here and elsewhere and I found links like the following: https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/ Sql Server select datetime without seconds
but I'm still having difficulty with my code:
SELECT FORMAT(MAX(date1), 'yyyy-mm-dd HH:mm') FROM table2
.
What am I doing wrong?
CodePudding user response:
Looking at the format
docs for dates and times and the date and time data types, we see mm
is minutes and MM
is months.
SELECT FORMAT(MAX(date1), 'yyyy-MM-dd hh:mm:ss') FROM table2
It might be faster to convert
, but that also means using obscure convert codes.
select convert(datetime2, max(date1), 20);
The different formats suggest table1 is a smalldatetime
and table2 is a datetime2
. If you have to do this conversion a lot, consider making your datetime columns all the same type.
create table test (
col_smalldatetime smalldatetime,
col_datetime datetime,
col_datetime2 datetime2
);
insert into test values ('2022-01-02 03:04:05', '2022-01-02 03:04:05', '2022-01-02 03:04:05');
select * from test;
col_smalldatetime col_datetime col_datetime2
2022-01-02 03:04 2022-01-02 03:04:05.000 2022-01-02 03:04:05.0000000