want condition on timepart of datepart

[miteshpanchal2006]

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.

 

[SOS]

Are you using this in Access or on SQL Server?

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

 

[miteshpanchal2006]

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:

 

[SOS]

No, you just use:

TimeValue([projectdone])

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

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

 

[miteshpanchal2006]

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

 

[SOS]

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

 

[miteshpanchal2006]

using Access 2007 and getting results from SQL server

 

[SOS]

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

 

[miteshpanchal2006]

yes it is linked sqL server table

 

[SOS]

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

 

[SOS]

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.

 

[miteshpanchal2006]

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

 

[SOS]

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).