Home > Mobile >  Format my date in a SQL query, namely to remove seconds?
Format my date in a SQL query, namely to remove seconds?

Time:06-17

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.

  • Related