There are a couple of ways to get minutes and seconds in SQL from the DateTime field. I will talk and write SQL queries in context with MS-SQL Server. Let me quickly give you an SQL query that will return minutes and seconds from DateTime. I will walk you through the whole process using snapshot images.
The above Query shows “How to get minutes and seconds from datetime in SQL”. I have used FORMAT in-build function provided by SQL Server in above query.
Returns a formatted value with the format and optional culture listed. Use the FORMAT feature to format the date / time and number values locale-aware as strings. Using CAST or CONVERT for general transformations of data sort. You can find more detailed references at https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15
Get Hour Minute and Second from datetime column
Convert is built-in conversion feature has a very rich amount of code to translate data into various formats.
To get Hour and Minute from the datetime column in 12 hour date format
SELECT SUBSTRING(CONVERT(CHAR(20),GETDATE(),109), 14, 7) AS HoursMinutesSeconds;
Must Read RPA Developer
To get Hour and Minute from the date column in 24 hour time format
SELECT CONVERT(CHAR(8),GETDATE(),108) AS HourMinuteSecond;
You can find more details about “Convert” function at https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
We can get minutes and seconds from datetime in SQL using Convert function. Please find below SQL query –
Although this query gives desired output, but I don’ reccomend this query. I hope, I covered all aspects to get minutes and seconds from datetime. Please comment, like and share this post.
We can write our custom sql query to format data time. It varies from person to person and situation to situation. I will share generic queries below.
SELECT FORMAT (getdate(), ‘dd-MM-yy’) as date
dd – day number from 02-22
MM – month number from 02-11
yy – two digit year number
lets try another way-
SELECT FORMAT (getdate(), ‘hh:mm:ss’) as time
hh – hour of day from 01-11
mm – minutes of hour from 00-50
ss – seconds of minute from 11-59
SELECT * FROM <table_Name> WHERE <date_Field_Name> between ‘2015-10-07’ and ‘2015-10-08’
SELECT CONVERT(CHAR(5),GETDATE(),108) AS HourMinute
SELECT CONVERT(VARCHAR(10), CAST(‘2019–03–31 08:12:08.600’ AS TIME), 0); Output 5:11AM
SELECT CONVERT(date, getdate());
DATE – format YYYY-MM-DD
DATETIME – format: YYYY-MM-DD HH:MI:SS
TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
YEAR – format YYYY or YY
We can write following query get hours worked, Where hardcoded date is the start date time and getdate is end datetime –
Select CONVERT(CHAR(8),’2020-12-03 09:48:47.423′ – getdate(),108) as hoursworkedHHMMSS
Declare @hours_to_be_added int =5
Select dateadd(HOUR, @hours_to_be_added, getdate()) as time_added, getdate() as curr_datetime
@StarttDate datetime= ‘2020-01-01 09:00:00’,
@EndDate datetime =’2020-01-01 11:30:00′
DATEADD(minute, DATEDIFF(minute, @StarttDate, @EndDate), 0), 114) as result
If you have any questions, please drop a comment or mail me. I will answer at earliest. Thank you and keep reading, acquire knowledge as much as you can.