Results 1 to 7 of 7

Thread: SQL with Dates

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2009
    Location
    Milton Keynes
    Posts
    29

    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
    James Roche aka PsYiOn
    www.psyion.info

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2009
    Location
    Milton Keynes
    Posts
    29

    Re: SQL with Dates

    Im using MS SQL Server 2005 Express
    James Roche aka PsYiOn
    www.psyion.info

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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)

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2009
    Location
    Milton Keynes
    Posts
    29

    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...
    James Roche aka PsYiOn
    www.psyion.info

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: SQL with Dates

    This will work:

    sql Code:
    1. 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

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    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
  •  



Click Here to Expand Forum to Full Width