Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Unanswered: ignore time portion of datetime field

    I have a PHP page where the user enters a date that represents the last day of a timesheet (ts_end) and the hours worked on that timesheet. That is then written into a table where the date is a datetime type. Because the user just enters a date, the time portion of the field is set to 00:00:00. In another place, I need to sum the columns for reports submitted between the beginning of a timesheet (ts_end -6 days) and the ts_end date.

    The problem is that chartreviewed values entered on the ts_end date are getting lost because the time part of the ts_end field is 00:00:00 and the time part of the dateentered for the chartreviewed value is not. For instance using 2/4/2004 as the ts_end date looses the 192 charts.

    Reporter activity charts dateentered
    2001576 20672 563 2004-01-29 13:55:51.000
    2001576 20665 202 2004-02-02 19:54:57.000
    2001576 20666 160 2004-02-03 22:48:11.000
    2001576 20667 192 2004-02-04 19:41:51.000

    I know I can revise the query to look for charts where the dateentered is less than dateadd(d,1,ts_end) and get the right values. It seems like there has to be a way though to tell sqlserver to ignore the time part of a datetime field when querying.

    Anybody know what it is?

    Thanks,
    Ursus

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: ignore time portion of datetime field

    select convert(char(10), getdate(), 120)

    In your case, replace the getdate() with the datetime column on the table.

  3. #3
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Re: ignore time portion of datetime field

    No joy. Converting the ts_end date

    dateentered between dateadd(d,-6,ts_end) and convert(char(10), ts_end, 120)

    yields the same results as before and converting the dateentered

    dateentered between dateadd(d,-6,ts_end) and convert(char(10), dateentered, 120)

    gives me some really weird results.

    Am I misunderstanding what you're telling me to do?

    Originally posted by joejcheng
    select convert(char(10), getdate(), 120)

    In your case, replace the getdate() with the datetime column on the table.

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: ignore time portion of datetime field

    Convert dateentered.

  5. #5
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Re: ignore time portion of datetime field

    Ah! Now I got it. Thanks.


    Originally posted by joejcheng
    Convert dateentered.

Posting Permissions

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