datepart(hh,end_tran_date) returns 0

Hi All,

I am calling this T-SQL function in SQL server 2005:
datepart(hh,end_tran_date). It returns a 0?????
This is the value of end_tra_date field: 1900-01-01 09:13:14.000

Does anyone know how to handle this? I just want to extract the time from this field and compate it to current time and get the difference in minutes.
As you can see the date there is set to 1900 -1-1 so I cannot use datediff()

 

I am not as familiar with sql server as I am with mysql and there isn't a datepart() function in mysql, I don't think. However, ther is the date_format() function. In mysql this:

Code:

select date_format('1900-01-01 09:13:14.000)', '%T');

returns this:

Code:

+-----------------------------------------------+
| date_format('1900-01-01 09:13:14.000)', '%T') |
+-----------------------------------------------+
| 09:13:14                                      |
+-----------------------------------------------+

Does that work for you? This will return the time in 24h time.

 

I guess I could have tried tackling the second part of your question as well .

I am not exactly sure what you want, so I will give you two solutions. If you want to return the difference in the numbers column between the two dates, I would use something like the following:

Code:

select time_format(timediff(date_format(sysdate(), '%T'), 
date_format('1900-01-01 09:13:14.000', '%T')), '%i') as 'Minutes';


+---------+
| Minutes |
+---------+
| 15      |
+---------+
1 row in set (0.00 sec)

But if you want to return the total difference in time between the two date_time values and then convert it to minutes, I would use the following:

Code:

select time_to_sec(timediff(date_format(sysdate(), '%T'), 
date_format('1900-01-01 09:13:14.000', '%T')))/60 as 'Minutes';


+----------+
| Minutes  |
+----------+
| 502.4000 |
+----------+
1 row in set (0.00 sec)

There might be more eloquent ways of doing this, but this gets the job done.