• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

First day of current month in sql

Hi Experts,

Can someone tell me how to get the first day of the current month in sql?

Example:

10/31/2013 - CurrentDate
10/01/2013 - First day of the CurrentDate
0
daintysally
Asked:
daintysally
  • 4
  • 3
  • 2
  • +1
1 Solution
 
YZlatCommented:
SELECT DATEADD(mm, DATEDIFF(m, 0, getdate()), 0)
0
 
Éric MoreauSenior .Net ConsultantCommented:
something like: SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS FirstDay
0
 
DcpKingCommented:
(the first day is always the first :)  )

so add (1 - today's day number) to today's date.

select dateadd(day, 1-day(getdate()), getdate()) as date

Open in new window


And for a date, cast it to one:

select cast(dateadd(day, 1-day(getdate()), getdate()) as date)

Open in new window


hth

Mike
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
daintysallyAuthor Commented:
I have tried all of your suggestions and I error on the dateadd function.  I have tried this:
(add_months(getdate(),-1)) and it returns 10/31/2013.
0
 
Éric MoreauSenior .Net ConsultantCommented:
what is your database engine? all these queries should work on Microsoft SQL Server.
0
 
Éric MoreauSenior .Net ConsultantCommented:
... which is the topic in which you post your question!
0
 
daintysallyAuthor Commented:
it's in a tool called Composite,  but all of the script is in sql
0
 
DcpKingCommented:
Then you're not running SQL Server.

If (add_months(getdate(),-1)) gives the end of last month then what about

add_days(    getdate(),    1 - day(getdate()    )

Mike
0
 
daintysallyAuthor Commented:
that's okay, figured it out

date_add(add_months(nameofdatefield,-1),+1)
0
 
YZlatCommented:
dateadd works fine on SQL Server
0
 
daintysallyAuthor Commented:
Figured solution on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now