How do I format my date in a SQL query, namely to remove seconds?-CodePudding

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

Demonstration of different datetime types.