First Date of Month

I need subquery that would return table with a column that contains first
date of month.
I would be prefer avoid using temp tables if possible.
I have a status table So I can do following
SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
FROM Status
WHERE Status.Date>='7/1/2005'
GROUP BY YEAR(Date), MONTH(Date)
I am not sure if this is best way to do this. Also I still need to get rid
of time part.
Thank you.Shimon,
You can use the following expression to calculate the first date of the
month based on a given datetime column dt_col:
CAST(CONVERT(CHAR(6), dt_col, 112) + '01' AS DATETIME)
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Shimon Sim" <shimonsim048@.community.nospam> wrote in message
news:%23rFHyo9hGHA.4144@.TK2MSFTNGP02.phx.gbl...
>I need subquery that would return table with a column that contains first
>date of month.
> I would be prefer avoid using temp tables if possible.
> I have a status table So I can do following
> SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
> DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
> FROM Status
> WHERE Status.Date>='7/1/2005'
> GROUP BY YEAR(Date), MONTH(Date)
> I am not sure if this is best way to do this. Also I still need to get rid
> of time part.
> Thank you.
>
>|||You can avoid string conversions doing it this way:
dateadd(month,datediff(month,0,dt_col),0
)
Steve Kass
Drew University
Shimon Sim wrote:

>I need subquery that would return table with a column that contains first
>date of month.
>I would be prefer avoid using temp tables if possible.
>I have a status table So I can do following
>SELECT YEAR(Date) AS DateYear, MONTH(Date) AS
>DateMonth,DateAdd(day,-DAY(MIN(Date))+1,MIN(Date)) as FirstDay
>FROM Status
>WHERE Status.Date>='7/1/2005'
>GROUP BY YEAR(Date), MONTH(Date)
>I am not sure if this is best way to do this. Also I still need to get rid
>of time part.
>Thank you.
>
>
>