Menu
There is a column in my Table. In which we are storing string value in format 'HH:MM'.During fetching records with this table every things works ok with
DATEDIFF(MINUTE, '00:00', ColumnName)
Problem is when we have Value greater than 23:59.
Its showing error like
Conversion failed when converting date and/or time from character string.
Can anybody suggest me the right approach for achieving this scenario.
593
To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1.
The DATEDIFF() function returns an integer value that represents the difference between the start date and end date, with the date part as the unit. If the result is out of range for the integer (-2,147,483,647), the DATEDIFF() function returns an error. Here, the DATEDIFF BIG() function should be used instead.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
If you are storing the value as something other than a time, why not just store the number of minutes and convert to whatever format you want on output?
Otherwise, I would suggest that you simply convert the value to minutes:
select (cast(left(ColumnName, 2) as int) * 60 +
cast(right(ColumnName, 2) as int)
) as Minutes
If you are not using date/time values, there is no requirement for using the functions specifically designed for them.
EDIT:
To handle hours longer than 99, use charindex():
select (cast(left(ColumnName, charindex(':', ColumnName) - 1) as int) * 60 +
cast(right(ColumnName, 2) as int)
) as Minutes
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
