-
Jan 2nd, 2010, 08:53 AM
#1
Thread Starter
Junior Member
SQL with Dates
Hello All,
I have a field in my database called Visit_Time this holds a date/Time value which shows the data and the time like this 10/10/86 14:21:56.
I need to select all the redords that have a data matching todays data and ignoring the time. i had tried this.
Code:
Dim tempTime As Date = Now.Date
claSql.sqlReader("SELECT [Visit_Time], [Type] FROM tblTraffic WHERE [Visit_Time]='" & tempTime & "'")
lblHitsToday.Text = claSql.table.Rows.Count
However my rowcount = 0 at runtime. There are many records in the table so obviously my statement is selecting nothing. Is there SQL syntax i can to select all the fields i want?
Regards
James
-
Jan 2nd, 2010, 09:32 AM
#2
Re: SQL with Dates
What is the database backend? It will make a difference in what the SQL will look like.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jan 2nd, 2010, 09:32 AM
#3
Thread Starter
Junior Member
Re: SQL with Dates
Im using MS SQL Server 2005 Express
-
Jan 2nd, 2010, 09:33 AM
#4
Re: SQL with Dates
You haven't told us which database system you are using... but you might not be using the correct delimiters, and you almost certainly are not using the correct format (except possibly by luck). edit: ok, with the info from your last post you are using the correct delimters
For an explanation and examples of what you should be doing, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)
-
Jan 2nd, 2010, 09:38 AM
#5
Thread Starter
Junior Member
Re: SQL with Dates
Si, i am using the ' before and after as stated in the link you posted. I think the problem is that the value in the database is dd/mm/yyyy hh:mm:ss and im comparing it to a value which is dd/mm/yyyy which is why im getting no macthes...
-
Jan 2nd, 2010, 09:43 AM
#6
Re: SQL with Dates
This will work:
sql Code:
SELECT [Visit_Time], [Type] FROM tblTraffic WHERE Convert(VARCHAR(10),[Visit_Time],101)='" & tempTime & "'"
Do you understand the Convert section, if not check books on line.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jan 2nd, 2010, 09:43 AM
#7
Re: SQL with Dates
For SQL Server you can use Convert to get part of a date/time, formatted in a particular way.
If you use this:
Code:
... WHERE Convert(Char(8),112,[Visit_Time])='" & tempTime & "'
..it should work as long as tempTime is a String formatted as yyyymmdd
edit: Gary's assumes that tempTime has a format of mm/dd/yyyy
Si, i am using the ' before and after as stated in the link you posted. I think the problem is that the value in the database is dd/mm/yyyy hh:mm:ss and im comparing it to a value which is dd/mm/yyyy which is why im getting no macthes...
No matter what you may think, the value in the database is not in any format at all - it is just a value.
Within SQL statements you must format dates in US or ISO formats - or use a method like shown above.
Last edited by si_the_geek; Jan 2nd, 2010 at 09:46 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|