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.
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);
How to select date and time without the seconds in mysql?
SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i') AS `formatted_date` FROM `table`;
How to remove seconds from datetime?
Solutions if need datetimes in output:
df = pd.DataFrame({'start_date_time': ["2016-05-19 08:25:23","2016-05-19 16:00:45"]})
df['start_date_time'] = pd.to_datetime(df['start_date_time'])
print (df)
start_date_time
0 2016-05-19 08:25:23
1 2016-05-19 16:00:45
Use Series.dt.floor
by minutes T
or Min
:
df['start_date_time'] = df['start_date_time'].dt.floor('T')
df['start_date_time'] = df['start_date_time'].dt.floor('Min')
You can use convert to numpy values
first and then truncate seconds
by cast to <M8[m]
, but this solution remove possible timezones:
df['start_date_time'] = df['start_date_time'].values.astype('<M8[m]')
print (df)
start_date_time
0 2016-05-19 08:25:00
1 2016-05-19 16:00:00
Another solution is create timedelta
Series from second
and substract:
print (pd.to_timedelta(df['start_date_time'].dt.second, unit='s'))
0 00:00:23
1 00:00:45
Name: start_date_time, dtype: timedelta64[ns]
df['start_date_time'] = df['start_date_time'] -
pd.to_timedelta(df['start_date_time'].dt.second, unit='s')
print (df)
start_date_time
0 2016-05-19 08:25:00
1 2016-05-19 16:00:00
Timings:
df = pd.DataFrame({'start_date_time': ["2016-05-19 08:25:23","2016-05-19 16:00:45"]})
df['start_date_time'] = pd.to_datetime(df['start_date_time'])
#20000 rows
df = pd.concat([df]*10000).reset_index(drop=True)
In [28]: %timeit df['start_date_time'] = df['start_date_time'] - pd.to_timedelta(df['start_date_time'].dt.second, unit='s')
4.05 ms ± 130 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
In [29]: %timeit df['start_date_time1'] = df['start_date_time'].values.astype('<M8[m]')
1.73 ms ± 117 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
In [30]: %timeit df['start_date_time'] = df['start_date_time'].dt.floor('T')
1.07 ms ± 116 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
In [31]: %timeit df['start_date_time2'] = df['start_date_time'].apply(lambda t: t.replace(second=0))
183 ms ± 19.7 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
Solutions if need strings repr of datetimes in output
Use Series.dt.strftime
:
print(df['start_date_time'].dt.strftime('%Y-%m-%d %H:%M'))
0 2016-05-19 08:25
1 2016-05-19 16:00
Name: start_date_time, dtype: object
And if necessary set :00
to seconds:
print(df['start_date_time'].dt.strftime('%Y-%m-%d %H:%M:00'))
0 2016-05-19 08:25:00
1 2016-05-19 16:00:00
Name: start_date_time, dtype: object
Ignore seconds and milliseconds FROM GETDATE() in SQL
I'd either use the DATEADD
/DATEDIFF
trick that Codo has shown or just cast it to smalldatetime
1:
select CAST(GETDATE() as smalldatetime)
I'd avoid anything that involves round-tripping the value through a string.
1It may be appropriate, at this time, to change your schema to use this data type anyway, if seconds are always irrelevant.
Strip seconds from datetime
You can do
DateTime dt = DateTime.Now;
dt = dt.AddSeconds(-dt.Second);
to set the seconds to 0.
Truncate seconds and milliseconds in SQL
There are a number of ways to go about doing this.
For example, you could convert the generated datetime
from GetDate()
to a smalldatetime
first, à la:
CAST(GetDate() AS smalldatetime)
To be clear, this will round the generated seconds up (or down) to the nearest minute depending up the value of the current second.
EDIT:
Alternatively, you can have SQL Server truncate a datetime
for you for a "cleaner" (READ: no rounding, since the value is pre-truncated) conversion to smalldatetime
:
CAST(DateAdd(minute, DateDiff(minute, 0, GetDate()), 0) AS smalldatetime)
How to Select only Date Hour and Minutes from 2017-09-27 15:39:36.000
Use the FORMAT
function to format the date:
SELECT FORMAT(CAST('2017-09-27 15:39:36.000' AS DATETIME), 'yyyy-MM-dd HH:mm')
-- 2017-09-27 15:39
Here is a list of available format specifiers.
Related Topics
SQL Comma-Separated Row with Group by Clause
Sqlite Alter Table Add Multiple Columns in a Single Statement
Postgres Not Using Index When Index Scan Is Much Better Option
Rodbc Temporary Table Issue When Connecting to Ms SQL Server
SQL Create Logon - Can't Use @Parameter as Username
How to Efficiently Delete Rows While Not Using Truncate Table in a 500,000+ Rows Table
How to See All the Tables in an Hsqldb Database
Bulk Insert Using Stored Procedure
Using Pivot in SQL Server 2008
Improve SQL Server Query Performance on Large Tables
Why Do Null Values Come First When Ordering Desc in a Postgresql Query
Add Business Days to Date in SQL Without Loops
Pass String Variable in R Script to Use It in SQL Statement
I Keep Getting the Error "Relation [Table] Does Not Exist"
Core Data Performance with Single Parent Entity
Lightweight SQL Database Which Doesn't Require Installation
How to Write a Constraint Concerning a Max Number of Rows in Postgresql