Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

1st day of the current month and first day of next month

Posted on 2009-06-30
4
Medium Priority
?
417 Views
Last Modified: 2012-05-07
Is there a way of building a string to my query can automatically calculate the first day of the current month and the fisrt day of next month?? something like the below would be great.

select CAST('01/01/' + STR(year(getdate())) AS datetime)
0
Comment
Question by:leap29
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24744339
Use date functions:
SELECT DATEADD(mm, 0, DATEDIFF(mm, 0, GETDATE()))

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24744349
There are other constructs, but I usually go with above as I can then adjust to previous or next months by simply changing the amount of months to add in the outer DATEADD.
-- next month on the 1st
SELECT DATEADD(mm, 1, DATEDIFF(mm, 0, GETDATE()))

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 24744383
Sorry, I gave you the wrong one.  That is the one I use when dealing with days.  This is for month.
-- this month on the 1st
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
 
-- next month on the 1st
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24744631
you can get date as well as day name also.




SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) as dt,DATENAME(Dw,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) as DayName
 
 
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0),DATENAME(dw,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)) as dayname

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question