hello,
I want the datepart result in hh:mm:ss (excluding date) Please see below example

Person Project Done
a 04/01/2010 04:45:00
b 03/29/2010 04:55:00
c 02/28/2010 05:05:00
d 02/25/2010 05:31:00

I want to result > 5:30:00

I'd used below query but only hour option available. Please help me in getting the result in HH:MM:SS

datepart (hh, projectdone) > 5 whre as i want to result which is > 5:30

Please help me in getting combined HH:mm.

Are you using this in Access or on SQL Server?

If in Access you can use TimeValue([YourFieldName])

Thanks for looking into this. I am using MS access
I've tried below constraint
--and timevalue (hh:mm, [a.projectdone]) = 5:30 and getting below error

incorrect syntax near hh:

No, you just use:

TimeValue([projectdone])

and if you want it formatted to only hours and minutes:

Format(TimeValue([projectdone]), "hh:mm")

Thanks for your quick responce but still i am confused.

I want the result with is = or > 5:30. i've tried below formula after your answer

and format(timevalue([a.starttime3]), "hh:mm") > "05:30"

and getting below error

timevalue is not a recognized built-in fuction name

Which version of Access are you using and where are you attempting to use this?

using Access 2007 and getting results from SQL server

Do you mean you are using a QUERY to a LINKED SQL Server table?

yes it is linked sqL server table

Hmm, it works for me with a linked SQL table on Access 2007 (see screenshots):


http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=31593&stc=1&d=1270222066

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=31594&stc=1&d=1270222066

Oh, and for MINUTES you really should use nn and not mm (m is for months). I know that it may work occasionally with m but to ensure that it returns the correct values you should use n for minutes.

Thanks for your time,

I am using Querry. and using below conditions

where a.keydoccheck = 18
and a.CheckedAsOf >= '4/02/2010'
and format(timevalue([a.starttime3]), "Short Time") > "05:30")

(where keydoccheck, checkedasof and stattime3 are filed name of particular table.)

your formula not working here :(

Thanks for your time,

I am using Querry. and using below conditions

where a.keydoccheck = 18
and a.CheckedAsOf >= '4/02/2010'
and format(timevalue([a.starttime3]), "Short Time") > "05:30")

(where keydoccheck, checkedasof and stattime3 are filed name of particular table.)

your formula not working here :(

Can you post a screenshot of your query (with the applicable parts visible) or post the SQL from it (by going to the SQL View).