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.you can use the function DATEPART. See Books Online for more information.|||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: hopefully you are not insulted by this remark, but didn't I post just the same answer as you did?|||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.

:)|||I am looking for a formatted date ("dd/mm/yy") from a datetime field
and group by the same.|||select convert(varchar(12), getDate(), 6) where the 6 stands for the format used. Look under CONVERT in Books Online for the formats.|||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

;)|||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?