Sql Server Select Datetime Without Seconds

Sql Server select datetime without seconds

In SQL Server this will work:

DECLARE @now [datetime]; 
SET @now = GETDATE();
SELECT
    CONVERT([varchar](10), @now, 105) + ' ' + 
    RIGHT('0' + CONVERT([varchar](2), DATEPART(HOUR, @now)), 2) + ':' +
    RIGHT('0' + CONVERT([varchar](2), DATEPART(MINUTE, @now)), 2);

A way to extract from a DateTime value data without seconds

For a solution that truncates using strings try this:

SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime)

---

CHAR(16) works only if our variable is converted to ODBC canonical format, as shown above by using 20 as the format specifier.

DECLARE @date DateTime = '2011 Nov 22 12:14:55';
SELECT CONVERT(Char(16), @date ,20) AS datetime

Results:

| datetime         |
|------------------|
| 2011-11-22 12:14 |

Then you simply cast back to a DateTime type to continue using the value.

NOTE: This is only viable for data types that do not carry TimeZone info.
Also type conversions to VarChar and back are usually LESS performant than using DateTime functions that use numeric operations internally.

Consider other solutions posted if performance is a concern or if you must retain timezone information.

How do I remove seconds from date time in SQL query

Try this:

   SELECT CONVERT(nvarchar, date, 101) + ' ' + left(RIGHT(CONVERT(nvarchar, date, 100), 7),5) + ' ' +RIGHT(CONVERT(nvarchar, date, 100), 2)

Remove seconds from datetime

Try the below

 SELECT CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+'/'
+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)
+'/'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)
+' '+CAST(DATEPART(HH,GETDATE()) AS VARCHAR)
+':'+CAST(DATEPART(MI,GETDATE()) AS VARCHAR)

also look at this Custom Date/Time formatting in SQL Server

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

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.

Store datetime without seconds using SQL in PHP

The datetime column uses the structure Y-m-d H:i:s

It will always store seconds. However you can use PHP's DateTime class to format dates like so:

$date = new DateTime();
echo $date->format('Y-m-d H:i'); // outputs 2017-08-18 17:05

When querying the DB, in the WHERE clause you can say:

...WHERE datecolumn = '".$date->format('Y-m-d H:i:00')."' etc

Example:

$date = new DateTime();
echo $date->format('Y-m-d H:i:s')."\n"; // 2017-08-18 17:09:15
echo $date->format('Y-m-d H:i:00')."\n"; // 2017-08-18 17:09:00

See it in action: https://3v4l.org/D47pA

Btw you can pass in your string into the DateTime constructor like

new DateTime($row['datecolumn']);

t-sql convert datetime to time only with NO SECONDS

Would this do it?

select CONVERT(varchar(15),CAST(GETDATE() AS TIME),100)

Just change out GETDATE() with your date variable.

How to convert datetime to varchar without seconds?

declare @date datetime
declare @str varchar(4000)

set @date = '2014-11-25 19:00'
set @str = convert(varchar, @date, 101)

or

set @str =  Convert(varchar(10), CONVERT(date, @date))

---

Related Topics