SQL FUNCTION USING DATE TIME
-
FIRSTTIMEIN LASTTIMEOUT 7:10:09:000PM 9:40:45:000PM 10:20:32:000PM 12:15:27:000AM From this,i will have to get only 7:10:09:000PM as FIRSTTIMEIN and 12:15:27:000AM as LASTTIMEOUT.I used min and max function.but it was not possible.help me
-
FIRSTTIMEIN LASTTIMEOUT 7:10:09:000PM 9:40:45:000PM 10:20:32:000PM 12:15:27:000AM From this,i will have to get only 7:10:09:000PM as FIRSTTIMEIN and 12:15:27:000AM as LASTTIMEOUT.I used min and max function.but it was not possible.help me
MIN and MAX are indeed the right functions to use, however, for them to work properly the data must be stored in a DATETIME field, because using strings to store those times will obviously yield string-based results for min & max. There is no way to calculate the min and max times from strings representing dates. Secondly, in order ot know that 12:15am is after 7:10pm you must also store the date date part along with the time part. Here is a simple test script that shows the process (and returns the results you want). You'll notice ive used yesterdays date for the 3 values before midnight, and today's date for the one after midnight.
WITH data(firstTimeIn,lastTimeOut )
AS
(
SELECT CAST('4 july 2010 7:10:09:000PM' AS DATETIME),CAST('4 july 2010 9:40:45:000PM' AS DATETIME)
UNION
SELECT CAST('4 july 2010 10:20:32:000PM' AS DATETIME),CAST('5 july 2010 12:15:27:000AM' AS DATETIME)
)
SELECT
MIN(firstTimeIn) as firstTimeIn,
MAX(lastTimeOut) as lastTimeOut
FROM dataResult: firstTimeIn: 2010-07-04 19:10:09.000 lastTimeOut: 2010-07-05 00:15:27.000
-
MIN and MAX are indeed the right functions to use, however, for them to work properly the data must be stored in a DATETIME field, because using strings to store those times will obviously yield string-based results for min & max. There is no way to calculate the min and max times from strings representing dates. Secondly, in order ot know that 12:15am is after 7:10pm you must also store the date date part along with the time part. Here is a simple test script that shows the process (and returns the results you want). You'll notice ive used yesterdays date for the 3 values before midnight, and today's date for the one after midnight.
WITH data(firstTimeIn,lastTimeOut )
AS
(
SELECT CAST('4 july 2010 7:10:09:000PM' AS DATETIME),CAST('4 july 2010 9:40:45:000PM' AS DATETIME)
UNION
SELECT CAST('4 july 2010 10:20:32:000PM' AS DATETIME),CAST('5 july 2010 12:15:27:000AM' AS DATETIME)
)
SELECT
MIN(firstTimeIn) as firstTimeIn,
MAX(lastTimeOut) as lastTimeOut
FROM dataResult: firstTimeIn: 2010-07-04 19:10:09.000 lastTimeOut: 2010-07-05 00:15:27.000
If at all possible don't use that string format. It will break in other locales. '2010-07-04 22:20:32:00' is the better format. It is always parsed correctly no matter what the locale.
Jason S Short, Ph.D. VistaDB Software, Inc.
-
If at all possible don't use that string format. It will break in other locales. '2010-07-04 22:20:32:00' is the better format. It is always parsed correctly no matter what the locale.
Jason S Short, Ph.D. VistaDB Software, Inc.