SQL DATETIME Math

Question

I am trying to do the following:

• Format GetDate() to display only the minutes
• Format a varchar column to display only the minutes
• Subtract the current time HH:MM:SS from GetDate() and the VarChar column

This is what I have

CONVERT(VARCHAR(5), GETDATE(), 108) - substring(convert(varchar(20), ColumnName, 9), 13, 5)

but I am getting this error and need some help please:

Operand data type varchar is invalid for subtract operator.

Answer 1

What you want is datepart(mi).

To get the minutes for getdate():

select datepart(mi, getdate())

To subtract a number of minutes from a datetime:

select dateadd(mi, - <minutes>, <datevalue>)

To remove the time from getdate(), just cast to date (in more recent versions of SQL Server):

select cast(getdate() as date)

To get the difference in minutes, use datediff:

select datediff(mi, <datestart>, <dateend>)

What are you really trying to accomplish?