quote:

---

Originally posted by X002548

SELECT RIGHT(CONVERT(varchar(7),GetDate(),111),2)+'/'+LEFT(CONVERT(varchar(7),GetDate(),111),4)


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

quote:

---

Originally posted by VPeters

Hi - Can anyone tell me how to remove the day and time from a datetime field, yet still keep the field functioning as a date for sorting/comparison purposes?? I was able to format the field as mm/yyyy, but it functions as a character string, and I am unable to convert it to a date format.

---

quote:

---

Originally posted by jsmith8858

Keep in mind that none of the "solutions" given here return nothing but string values, not actual dates. You should work with date values in sql server and worry about formatting at the front end. To deal with months only, simply convert the datetime to the first day of the month or something like, and at your front-end you can easily format that any way you want. Now it compares and sorts as you expect. If you convert your dates to mm/yyyy string values, they will sort like this:

11/2006
11/2007
12/2006
12/2007

when I suspect that you really want:

11/2006
12/2006
11/2007
12/2007






- Jeff
http://weblogs.sqlteam.com/JeffS

---

select [Month], sum(...), sum(...) .. etc.
from
(
  select dateadd(month, datediff(month, 0, YourDate),0) as [Month], ... other data ....
  from yourtable
) x
group by [Month]

quote:

---

Originally posted by jsmith8858

The solution is simple: Round the data to the nearest month, but keep it all as datetime's.

This expression will take any date and return the 1st day of the month:

select dateadd(month, datediff(month, 0, @date),0)

so just wrap that expression around your dates like this:

select dateadd(month, datediff(month, 0, YourDate),0) as [Month], ... other data ....
from yourtable

once you verify for sure that the Month column is returning the first day of the month for your data, just wrap it in a derived table and group on it:

select [Month], sum(...), sum(...) .. etc.
from
(
  select dateadd(month, datediff(month, 0, YourDate),0) as [Month], ... other data ....
  from yourtable
) x
group by [Month]

and there you go. now, Month is an actual DateTime and always the first day of each month, it will sort and compare correctly, and if you group on it you get 1 row per month returned. And because it is still an actual datetime, it is easy to format it as "mm/yyyy" or whatever you want at the front-end.

Hope this helps.

- Jeff
http://weblogs.sqlteam.com/JeffS

---

quote:

---

Originally posted by VPeters

Thank you Brett!!! This is EXACTLY what I needed!

---

quote:

---

Originally posted by X002548

quote:

---

Originally posted by VPeters

Thank you Brett!!! This is EXACTLY what I needed!

---

I really need to read the thread more closely

---

quote:

---

Originally posted by VPeters
I was able to format the field as mm/yyyy, but it functions as a character string, and I am unable to convert it to a date format.

---

quote:

---

Originally posted by VPeters

You know, I got so off track with all of this date nonsense that I completely forgot I wanted to attempt to do just that - find the first of the month. Perfect. Thanks!

---