How Do I Search Sql Server Database Based On Time Values

Question

0.00/5 (No votes)

See more:

Hello everyone,

I am working on a time/attendance tracking application, and it is required that the user will want to search based on the time and date a user signs in, i have implemented search by date functionality, i still could not figure how to do it with time.

please i will appreciate your assistance. Thanks in advance

Posted 11-Mar-15 4:20am

Uwakpeter

Add a Solution

Comments

OriginalGriff 11-Mar-15 10:59am

How have you done the date?
Remember, we have no idea how your database is organised, or what values you enter to it - so we can't just say "do this" and it'll work. There are as many wasy to do this as there are programmers, nearly! :laugh:

Uwakpeter 11-Mar-15 11:19am

in my table, i have a field "ActionDate" with datetime type.

OriginalGriff 11-Mar-15 11:25am

And?
How did you do it when you "implemented search by date functionality"?

Uwakpeter 11-Mar-15 11:35am

i used the between keyword

OriginalGriff 11-Mar-15 11:38am

How about you just post the relevant code fragment?

Uwakpeter 11-Mar-15 11:57am

This is what i used: select * from AuditTrail where
ActionDate between '2015-03-10 13:52:59.380' and '2015-03-10 13:52:59.380'

OriginalGriff 11-Mar-15 12:17pm

That isn't going to select a lot of values, is it?
In fact, it will return the same results as

select * from AuditTrail where ActionDate = '2015-03-10 13:52:59.380'

Because the start and end dates are identical...

ZurdoDev 11-Mar-15 11:43am

Since we can't see any of your code we can't tell why it doesn't work.

Uwakpeter 11-Mar-15 11:57am

This was what i used: select * from AuditTrail where
ActionDate between '2015-03-10 13:52:59.380' and '2015-03-10 13:52:59.380'

ZurdoDev 11-Mar-15 12:34pm

Those are the same. Why?

Uwakpeter 11-Mar-15 12:35pm

i created the database today, so i dont have previous record

ZurdoDev 11-Mar-15 12:47pm

OK. So, what is your question then?

Uwakpeter 11-Mar-15 12:52pm

i want to able to search by time values, say from 8a.m to 9a.m. i thought of storing the resumption time and closure time as string, but i am not sure whether storing it as string type will yield the required result.

ZurdoDev 11-Mar-15 12:55pm

No, don't store dates as string.

To search between 8 and 9 all you do is
BETWEEN '2015-03-10 8:00' AND '2015-03-10 9:00'

It seems like you should have gotten that on your own so I'm a little confused and wonder if you really are asking something different.

Uwakpeter 11-Mar-15 13:03pm

i want to be able to extract the time part and pass it as parameter so i could use it in the C# environment

ZurdoDev 11-Mar-15 13:08pm

'2015-03-10 ' + @someTimeVariable

Uwakpeter 11-Mar-15 13:25pm

in the C# environment, users are provided with two textboxes which they can search from using two different time intervals, say from 8a.m to 9a.m. Concatenating @someTimeVariable to date i am not sure this will archieve what i wanted. i want time parameter on its own without being concatenated to date. Please can this be possible?

ZurdoDev 11-Mar-15 13:26pm

Are you storing the values in the db as DateTime or just Time? What is the data type in the db?

If you want to search for just time and not the date part then you can use the CONVERT function to strip off the date part and have just time. Google for examples.

Uwakpeter 11-Mar-15 13:31pm

i am storing as DateTime, i didnt know about Time type, apart from timespan

ZurdoDev 11-Mar-15 13:36pm

If you store as DateTime then as you know the date is always part of it. The Time type was introduced in Sql 2008 as well as the Date type.

If you have 2008 or newer, to get just the time simply do:
select cast(GETDATE() as time)
or older versions of sql:
select convert(char(5), GETDATE(), 108)

Uwakpeter 12-Mar-15 3:18am

RyanDev, Thanks alot, this works, just that it comes out in this format:13:52:59.3800000. Please I want to be able to display in 24hrs format: e.g. : 01:52p.m using the above value. Please any futher assistance will be appreciated. Thanks

ZurdoDev 12-Mar-15 8:14am

Display is something done in your UI, not in SQL.

Uwakpeter 12-Mar-15 11:29am

Ok, i am clear on that, Thanks alot, i really appreciate.

ZurdoDev 12-Mar-15 11:30am

You're welcome.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2015-03-12T02:15:00

Solution 1

As mentioned in the comments, if you have SQL 2008 or newer, to get just the time simply do:

SQL

select cast(GETDATE() as time)

or older versions of sql:

SQL

select convert(char(5), GETDATE(), 108)

Posted 12-Mar-15 2:15am

ZurdoDev