Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Question Unanswered: Getting rid of the time value from a datetime field

    I want to get rid of the time stamp in a datetime field and also i need to include that in a group by query.

    For Ex:

    select join_dt, avg(salary) from employee
    group by join_dt

    Here in the above example, join_dt is a datetime field, i need some function similar datepart which gives only the date part of the join_dt.

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    you can use the function DATEPART. See Books Online for more information.
    Johan

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I think you just answered you own question...

    why don't u use datepart?

    you could do something like this:



    select join_dt, avg(salary) from employee
    group by datepart("datepartchoice",join_dt)


    or are u trying to say something else?
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    @Patrick: hopefully you are not insulted by this remark, but didn't I post just the same answer as you did?
    Johan

  5. #5
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    No offence taken, I was just wondering what was getbabs really asking, since his question was already containing the answer :

    getbabs>>i need some function similar datepart which gives only the date part of the join_dt.

    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  6. #6
    Join Date
    Aug 2003
    Posts
    5
    I am looking for a formatted date ("dd/mm/yy") from a datetime field
    and group by the same.

  7. #7
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    select convert(varchar(12), getDate(), 6) where the 6 stands for the format used. Look under CONVERT in Books Online for the formats.
    Johan

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think the real problem here is why does join_dt have a time component

    i could see the need for a time component in other datetime fields, but not this one

    if you need to use DATEPART in order to use GROUP BY on the date portion only, this a sure sign that you have been manouevered into a very bad situation by poor design or coding

    using DATEPART usually rules out indexes, and the query will perform poorly

    do yourself a favour, and reset the time portion of all the join_dt to midnight

    then you can go ahead and write simple queries like this --


    select join_dt, avg(salary) from employee
    group by join_dt


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2002
    Location
    UK
    Posts
    1
    As an extension to Rudy's post, you could consider an additional time column if time is really required. Update the time column with the time component from the join_dt field and then blank the join_dt field to midnight. Having said that, I agree with him inasmuch as what's a time doing in a join 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
  •