Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
darvelo
Starting Member
9 Posts |
 Posted - 2006-07-19 : 17:13:21
|
| Hey, everyone. I'm new to database programming in SQL Server, and I currently have the 2005 express edition. I was wondering whether it was possible to check whether one time (h:mm AMPM) is less or greater than another (the other in this case being the GETDATE). I already made a user-defined function in order to extract the time from a datetime, since SQL Server doesn't support the time data type. The thing is that the value is returned as a char(8) in the 12-hour style HH:MM AMPM format and I'm sure that's what's screwing up the comparison function. What would I need to do to compare two times (24-hour or 12-hour, whichever is easier)? |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-07-19 : 20:06:46
|
| [code]print 'The Diff between ' + convert(varchar(25), getdate()) + ' And Jul 19 2006 9:34PM is : ' Print 'Hours : ' + convert(varchar(10), (datediff (hh,getdate() , 'Jul 19 2006 9:34PM'))) + ' ' + 'Minutes : ' + convert(varchar(10),((datediff (n,getdate() , 'Jul 19 2006 9:34PM')) - (datediff (hh,getdate() , 'Jul 19 2006 9:34PM'))*60))[/code]Srinika |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-19 : 21:09:15
|
convert both time to same date (1900-01-01) and compare.declare @time1 datetime, @time2 datetimeselect @time1 = '20060701 02:27:35.35', @time2 = getdate()select tm1, tm2, case when tm1 = tm2 then 'tm1 = tm2' when tm1 > tm2 then 'tm1 > tm2' else 'tm1 < tm2' end as [Diff]from( select dateadd(day, -datediff(day, 0, @time1), @time1) as tm1, dateadd(day, -datediff(day, 0, @time2), @time2) as tm2) t/* RESULT :tm1 tm2 Diff ----------------------- ----------------------- --------- 1900-01-01 02:27:35.350 1900-01-01 09:07:22.260 tm1 < tm2*/
KH |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
darvelo
Starting Member
9 Posts |
Posted - 2006-07-20 : 10:43:01
|
quote:
Originally posted by khtan
declare @time1 datetime, @time2 datetimeselect @time1 = '20060701 02:27:35.35', @time2 = getdate()select tm1, tm2, case when tm1 = tm2 then 'tm1 = tm2' when tm1 > tm2 then 'tm1 > tm2' else 'tm1 < tm2' end as [Diff]from( select dateadd(day, -datediff(day, 0, @time1), @time1) as tm1, dateadd(day, -datediff(day, 0, @time2), @time2) as tm2) t/* RESULT :tm1 tm2 Diff ----------------------- ----------------------- --------- 1900-01-01 02:27:35.350 1900-01-01 09:07:22.260 tm1 < tm2*/
@khtan, I definitely like the simple way you present this function, and I would really like to use that in a CREATE FUNCTION command to return a scalar value to my application in a positive or negative value (returning an integer or boolean bit). Sorry I was not more clear on this.. is there a simple way to convert this code to that method? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-20 : 18:02:08
|
Have you take a look of F_TIME_FROM_DATETIME in the link i posted ? MVJ has created a function for this purpose.
KH |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-20 : 18:14:34
|
If you need a boolean result, this would do it.select case when dbo.F_TIME_FROM_DATETIME('Jul 19 2006 9:34 Pm') > dbo.F_TIME_FROM_DATETIME(getdate()) then 1 else 0 endCODO ERGO SUM |
|
|
|
darvelo
Starting Member
9 Posts |
Posted - 2006-07-21 : 08:40:01
|
Thank you very much!! The code did precisely what I needed. This forum is the best.  |
|
|
|
|
|
|
|
|
Compare two times in SQL Express