Results 1 to 6 of 6

Thread: datepart (XP)

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    datepart (XP)

    I have a table that is linked to a sql server. There is a date field that is formatted as general date in the table. I'm trying to write a query for a specific date. I'm enterying 10/03/2005 as the criteria, but because there records with different times for that date, i'm not getting any results.

    I tried DatePart("mm/dd/yyyy",[timestamp]) but I'm getting an error. How can I get all records with time stamp of 10/03/2005, regardless of time.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: datepart (XP)

    Try using <code>Int([timestamp])</code> or <code>CLng([timestamp])</code>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datepart (XP)

    I get serial date (38363).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: datepart (XP)

    That's how a date is actually stored. If you set the Format property of the column to Short Date, you'll see a date again.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datepart (XP)

    OK - I'm still trying CLng. Had already tried the short format trick. I'm still not getting any results for when I'm putting in criteria of 10/3/2005. If I change criteria to Between 10/3/2005 and 10/4/2005 I get just the results for 10/3, but nothing for 10/4.
    Ultimately, I want this to be a parameter query where user enters 10/3/2005 and filter out the 3,000,000 records I don't need to look at.

    Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: datepart (XP)

    You could use

    Between [Enter Date] And [Enter Date]+1

    as criteria. The user will have to enter the parameter only once. You can also refer to a text box txtDate on a form frmSomething by using Forms!frmSomething!txtDate instead of [Enter Date].

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •