Need SQL to derive DATETIME as 1st Day of Last Month

[btturner]

Need SQL to derive DATETIME as 1st Day of Last Month

EG.. If it's Aug 12 2004 I need a single SQL statement to derive: '2004-07-01 00:00:00.000'

thanks in advance

 

[despierto]

CAST(CAST(MONTH('Aug 12 2004') as varchar(2)) + '/01/' + CAST(YEAR('Aug 12 2004') AS varchar(4)) as datetime)

 

[SQLBill]

Here's how I come up with the first of the previous month (I did not create this, I got it from another poster on Tek-Tips but I don't remember who to give credit to).

DECLARE @start_dt SMALLDATETIME

IF MONTH(GETDATE()) = 1
  BEGIN
    SELECT
      @start_dt = '12/1/' + CONVERT(CHAR(4), YEAR(GETDATE()) - 1)
  END
ELSE 
  BEGIN
    SELECT
      @start_dt = CONVERT(CHAR(2), MONTH(GETDATE()) - 1) + '/1/' + CONVERT(CHAR(4), YEAR(GETDATE()))
  END

Still working on another way of doing it, but here's a start:

SELECT DATEADD(MONTH,-1,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE(),0))

The first DATEADD changes 08 to 07. The second DATEADD changes the time to 00:00:00.000. Now I just need to get the day figured out.

-SQLBill

 

[despierto]

Whoops. Missed the part about <i>last month</i>. Even though SQLBill already got it, I modified mine to work the way you want:

CAST(CAST((CASE MONTH('Aug 12 2004') WHEN 1 THEN 12 ELSE MONTH('AUG 12 2004')-1 END) AS VARCHAR(2)) + '/01/' + CAST((CASE MONTH('Aug 12 2004') WHEN 1 THEN YEAR('Aug 12 2004') - 1 ELSE YEAR('Aug 12 2004') END) AS VARCHAR(4)) AS DATETIME)

 

[SQLBill]

Check this out...it's really not pretty and I haven't tested efficiency. But it seems to work.

select dateadd(month,-1,(dateadd(day, -(datepart(day,getdate()-1)), (dateadd(day,datediff(day,0,getdate()),0)))))

-SQLBill

Oh the things you can do with code.

 

[ESquared]

SELECT Convert(datetime,Convert(int,Convert(float,DateAdd(m,-1,GetDate()-Day(GetDate())+1))))

-- and even shorter:

SELECT DateAdd(m,-1,Convert(varchar(8),GetDate(),112))-Day(GetDate())+1

-------------------------------------
A sacrifice is harder when no one knows you've made it.