Date : First day of last month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date : First day of last month

Author

Topic

raphaelrivest
Starting Member

14 Posts

Posted - 2008-06-02 : 13:40:12

Hello all,

Is there a way to set a variable that includes the first day of the last month.

For example, we are june 2nd. I want a function that returns - for every day in june - 2008/05/01.

Thanks,

Raph.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:59:17

SELECT DATEADD(dd,(-1) * (DATEPART(dd,GETDATE()) - 1) ,DATEADD(mm,-1,GETDATE()))

raphaelrivest
Starting Member

14 Posts

Posted - 2008-06-02 : 14:17:40

Thanks, visakh16!

quote:
Originally posted by visakh16

SELECT DATEADD(dd,(-1) * (DATEPART(dd,GETDATE()) - 1) ,DATEADD(mm,-1,GETDATE()))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 14:20:07

quote:
Originally posted by raphaelrivest

Thanks, visakh16!

quote:
Originally posted by visakh16

SELECT DATEADD(dd,(-1) * (DATEPART(dd,GETDATE()) - 1) ,DATEADD(mm,-1,GETDATE()))

You're welcome. Make sure you test it well. I've tested this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-04 : 12:05:10

or

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

Madhivanan

Failing to plan is Planning to fail

umermariner
Starting Member

4 Posts

Posted - 2011-03-16 : 15:41:37

Hello guys,
I think I need a little more help :(...
For the query below, I need to get data from last month. Right now, everytime I have to change the query (the dates at the bottom), and then run it to get the right results. I just came across this thread, and it seems like it can be of great help for me. However, I am not able to make it work with the two solutions above. All I want is first date of previous month as the first evttime, and last day of previous month as the higher limit.

I will appreciate if anyone can help me with that.

select s.sic as "SIC Code", s.cat_name as "SIC Name", count(*) as 'Requests'
from clientservices.p417_caller_revenue_summary r, pmartin.category_names s
where r.evtcode = 4
and r.evtnumber = s.sic
and evttime between unix_timestamp(20110201)
and unix_timestamp(20110301)
group by s.sic

Thanks again.

umermariner

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-16 : 16:00:23

The post is quite old, any how Check this

select s.sic as "SIC Code", s.cat_name as "SIC Name", count(*) as 'Requests'
from clientservices.p417_caller_revenue_summary r, pmartin.category_names s
where r.evtcode = 4
and r.evtnumber = s.sic
And unix_timestamp Between dateadd(day,-day(getdate())+1,DATEADD(month,-1,getdate())) and dateadd(day,-day(getdate()),getdate())
Group by s.sic,s.cat_name

Cheers
MIK

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-16 : 16:06:17

The first day of the last month?

SET @d = 1

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

umermariner
Starting Member

4 Posts

Posted - 2011-03-17 : 13:19:59

I tried, but its giving me this error msg :(
1054 - Uknown column 'unix_timestamp' in 'where clause'
Any idea?

umermariner

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-17 : 13:53:07

You gotta agree, my formatting looks nice. Now, let us hope it works too!!

select 
   s.sic as "SIC Code", 
   s.cat_name as "SIC Name", 
   count(*) as 'Requests'
from 
   clientservices.p417_caller_revenue_summary r, 
   pmartin.category_names s
where 
   r.evtcode = 4
   and r.evtnumber = s.sic
   and evttime between dateadd(mm,datediff(mm,0,getdate())-1,0)
      and dateadd(mm,datediff(mm,0,getdate()),-1)
group by 
   s.sic

umermariner
Starting Member

4 Posts

Posted - 2011-03-17 : 16:21:37

Your formatting is definitely breath taking ;).. however; didnt work :(

getting the error
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate())-1,0)
and dateadd(mm,datediff(mm,0,getdate()),-1)
group by
' at line 11

and.. I am looking at the query, but dont find anything :(

umermariner

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-18 : 07:23:46

MySQL! I didn't realize you were working with MySQL. You could try to replace "getdate()" with "current_timestamp". However, I have not worked with MySQL; this forum is exclusively for Microsoft SQL, so I doubt if many others here have. You may have better chances of getting good responses at http://www.dbforums.com/

umermariner
Starting Member

4 Posts

Posted - 2011-03-18 : 09:44:56

ohhhhhhhh.. my bad..
Thanks anyway :)

umermariner

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-18 : 12:43:15