How can I select the first day of a month in SQL?

Question

I just need to select the first day of the month of a given DateTime variable.

I know it's quite easy to do using this kind of code:

select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) 
+ '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME)

But unfortunately, this is not very elegant, and not very fast either.

Is there a better way to do this? I'm using SQL Server 2008.

score 711 · Accepted Answer · answered Oct 05 '09 at 15:32

711

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

answered Oct 05 '09 at 15:32

LukeH

252,910
55
358
405

22

[There is a cardinality estimation bug to be aware of if using this approach](http://stackoverflow.com/a/18242413/73226) – Martin Smith Aug 15 '13 at 14:01
79

It should be noted the bug mentioned by Martin Smith can "only" affect performance, not correctness. – Olson.dev Mar 05 '14 at 21:19
15

Here is an [explanation on why and how this works](http://www.jasonstrate.com/2010/03/grouping-and-rounding-dates/). – RubberDuck Oct 15 '14 at 19:24
Discovered this does not work with out of range datetime2 values. `select DATEADD(month, DATEDIFF(month, 0, cast('0002-01-01 00:00:00.0000000' as datetime2)), 0) ` will throw an error. – David Oct 01 '15 at 16:58
54

In case anyone is wondering `SELECT EOMONTH(@mydate) AS EndOfMonth` will give you the last day of the month. – hallizh Nov 03 '15 at 13:47
1

The noted cardinality estimation bug just caused 40 queries to pile up and a system to have an outage, so I'd strongly consider finding another method if performance / availability is important to you. – Sam Jul 28 '16 at 15:58
3

How can I determine if the noted cardinality estimation bug will affect me? It is marked as fixed in 2010. Does that mean that only SQL Server 2012 and newer are safe, or is it possible that a 2008 server may have been patched? – Jon May 05 '17 at 18:05
I have checked this query in `w3` and this works fine but when I run the same in my phpmyadmin I got this error `#1305 - FUNCTION dataabseName.DATEADD does not exist` – Sachin Shah Oct 17 '19 at 18:35
@SachinShah This question is about Microsoft SQL Server, not MySQL – Reversed Engineer May 27 '22 at 10:37

score 178 · Answer 2 · answered Oct 22 '14 at 10:51

178

In addition to all the above answer, a way based on a function introduced in sql 2012

SELECT DATEFROMPARTS(YEAR(@mydate),MONTH(@mydate),1)

answered Oct 22 '14 at 10:51

Jithin Shaji

5,483
5
24
46

score 111 · Answer 3 · edited Jun 11 '19 at 19:22

111

Starting with SQL Server 2012:

SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))

edited Jun 11 '19 at 19:22

ChrisG

1,228
4
17
34

answered Aug 04 '14 at 18:12

Marcos Krucken

1,111
1
7
2

12

This appears to only work on 2012 and later. http://msdn.microsoft.com/en-us/library/hh213020.aspx – Josh Yeager Sep 01 '14 at 15:56
2

Has the advantage that it mentions the source date only once, so neater if that is derived from a function call. – Ian Horwill Oct 04 '18 at 11:12
Simple! I like it – Reversed Engineer May 27 '22 at 10:44

score 15 · Answer 4 · answered Oct 05 '09 at 15:34

15

The casting of a string (i.e. "5/1/2009") to datetime is certainly more legible but we found code a while back that would return the first of the month...

DECLARE @Date DATETIME
//...
SELECT DATEADD(mm, DATEDIFF(mm,0,@Date), 0)

answered Oct 05 '09 at 15:34

Mayo

10,176
6
42
90

score 14 · Answer 5 · answered Apr 02 '15 at 10:08

14

Simple Query:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) 
-- Instead of GetDate you can put any date.

answered Apr 02 '15 at 10:08

Abhishek Gupta

525
4
6

I just tried it, it works. Thank you – Quince Ngomane Nov 25 '21 at 06:49

score 6 · Answer 6 · answered Oct 05 '09 at 15:33

It is probably quite fast. Why not create it as a sql function.

CREATE FUNCTION [dbo].[GetFirstDayOfMonth] ( @InputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

END
GO

PoloSoares · Answer 7 · 2014-12-10T06:53:58.070

6

SELECT @myDate - DAY(@myDate) + 1

edited Dec 10 '14 at 06:53

answered Jul 10 '14 at 01:27

PoloSoares

197
1
6

Really simple and elegant solution but keep in mind that this also returns the time portion of the date if specified in the variable. – kuklei Oct 17 '14 at 15:52
I couldn't get this to work. Besides the extra closing bracket, I get this error: `Operand type clash: date is incompatible with int`. I guess it's because you're trying to use the `-` operator on a date? – Sam Dec 10 '14 at 03:26
I don't know how good it is in terms of performance, but it certainly does the job. – salcoin Oct 20 '15 at 15:39
@Sam, you've got a clash because simple arithmetic (+, -) works on datetimes, not on dates. – darlove Dec 11 '19 at 17:43

score 6 · Answer 8 · edited Nov 19 '19 at 18:15

6

This might be a new function, but you can also use old functions :

select DATEFROMPARTS(year(@mydate),month(@mydate),'01')

If the date in the variable was for example '2017-10-29' it would return a date of '2017-10-01'

https://docs.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-ver15

edited Nov 19 '19 at 18:15

Standin.Wolf

1,189
1
9
30

answered Nov 19 '19 at 17:14

Luc

61
1
1

score 5 · Answer 9 · edited Apr 15 '19 at 20:43

5

First and last day of the current month:

select dateadd(mm, -1,dateadd(dd, +1, eomonth(getdate()))) as FirstDay, 
eomonth(getdate()) as LastDay

edited Apr 15 '19 at 20:43

Paul Roub

35,848
27
79
88

answered Apr 15 '19 at 20:31

nicolai koroslev

61
1
3

score 4 · Answer 10 · answered Aug 02 '13 at 20:55

4

This works too:

    SELECT DATEADD(DAY,(DATEPART(DAY,@mydate)-1)*(-1),@mydate) AS FirstOfMonth

answered Aug 02 '13 at 20:55

Alan Burstein

7,544
1
13
18

score 3 · Answer 11 · edited May 23 '16 at 17:57

3

Please use this

For Server 2012

DATEFROMPARTS(year('2015-06-30'),month('2015-06-30'),1)

Before Server 2012

select  cast(cast(year('2015-06-30') as varchar(4))+'-'+ cast(month('2015-06-30') as varchar(2))+'-01' as smalldatetime)

edited May 23 '16 at 17:57

cuongle

71,850
28
140
203

answered Jun 26 '15 at 06:53

adnan umar

99
5

score 2 · Answer 12 · answered Sep 02 '15 at 19:58

I used GETDATE() as a date to work with, you can replace it with the date which you need.
Here's how this works: First we format the date in YYYYMMDD... format truncating to keep just the 6 leftmost characters in order to keep just the YYYYMM portion, and then append '01' as the month - and voila! you have the first day of the current month.

SELECT CAST(CONVERT(VARCHAR(6),GETDATE(),112) +'01' AS DATETIME) AS StartOfMonth

BTW, performance is great on this!

Brian Widdoes · Answer 13 · 2018-10-30T16:48:59.057

2

DECLARE @startofmonth date
SET @startofmonth = DATEADD(dd,1,EOMONTH(Getdate(),-2))

The -2 will get you the first day of last month. ie, getdate() is 10/15/18. Your results would be 9/1/18. Change to -1 and your results would be 10/1/18. 0 would be the start of next month, 11/1/2018.. etc etc.

or

DECLARE @startofmonth date
SET @startofmonth = DATEADD(dd,1,EOMONTH(@mydate,-1))

edited Oct 30 '18 at 16:48

answered Oct 30 '18 at 16:39

Brian Widdoes

33
5

score 2 · Answer 14 · edited Dec 04 '18 at 15:25

2

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

edited Dec 04 '18 at 15:25

Suraj Rao

28,850
10
94
99

answered Dec 04 '18 at 15:20

r-magalhaes

420
2
9
17

But the OP is looking for the first day of the month. For last day, you could just use EOMONTH() - has been this way since 2012. – deutschZuid Feb 14 '19 at 21:51

score 2 · Answer 15 · edited Aug 01 '19 at 14:12

2

This query should work very well on MySQL:

SELECT concat(left(curdate(),7),'-01')

edited Aug 01 '19 at 14:12

Uladz Kha

1,748
4
28
52

answered Aug 01 '19 at 12:57

user11869348

21
1

score 2 · Answer 16 · edited Mar 10 '22 at 21:00

2

If you would like to go for SQL Server 2012+ you can try solution I used:

SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -1, GETDATE())))

edited Mar 10 '22 at 21:00

Dale K

21,987
13
41
69

answered Sep 30 '21 at 18:34

dariuszewski

31
2

Ariel T · Answer 17 · 2013-12-13T15:54:30.820

1

Future googlers, on MySQL, try this:

select date_sub(ref_date, interval day(ref_date)-1 day) as day1;

edited Dec 13 '13 at 15:54

answered Dec 05 '13 at 17:08

Ariel T

2,673
1
19
21

4

This is a `sql-server` question, `date_sub` and `interval` are `mysql`. – OGHaza Dec 12 '13 at 17:31
Good, point, I edited the response. I think it's still relevant for the thread. – Ariel T Dec 13 '13 at 15:55

score 1 · Answer 18 · answered Jun 15 '17 at 12:21

If you are looking at this today, and using SQL server 2012 or newer you have the EOMONTH function which makes things easier:

SELECT DATEADD(day, 1, EOMONTH(DATEADD(month, -1, GETDATE()))) as firstdateofmonth

You can change GETDATE() with whatever date variable you want.

score 1 · Answer 19 · edited Aug 20 '17 at 08:29

1

Here we can use below query to the first date of the month and last date of the month.

SELECT DATEADD(DAY,1,EOMONTH(Getdate(),-1)) as 'FD',Cast(Getdate()-1 as Date)
as 'LD'

edited Aug 20 '17 at 08:29

Jérémie B

10,129
1
23
39

answered Aug 20 '17 at 08:15

Pradeep Thakur

11
2

score 1 · Answer 20 · answered Sep 06 '17 at 11:12

1

If using SQL Server 2012 or above;

SELECT DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE())))

answered Sep 06 '17 at 11:12

user692942

15,667
7
74
164

score 1 · Answer 21 · edited Feb 22 '19 at 11:38

1

Try executing the following query:

SELECT DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE-INTERVAL 1 DAY),INTERVAL 1 DAY),INTERVAL -1 MONTH)

edited Feb 22 '19 at 11:38

Harshith Rai

2,958
7
20
35

answered Feb 22 '19 at 10:47

divya

11
1

score 0 · Answer 22 · answered Mar 12 '15 at 07:30

0

select CONVERT(date,DATEADD(dd,-(DATEPART(dd,getdate())-1),getdate()),120)

This function will provide you date part of start date of the month

answered Mar 12 '15 at 07:30

Tushar

1

score 0 · Answer 23 · answered Apr 05 '15 at 21:09

SELECT DATEADD (DAY, -1 * (DAY(GETDATE()) - 1), GETDATE())

.....................................................................

If you dont want the time, then convert it to DATE or if want to make to time to 0:00:00, Convert to DATE and then back to DATETIME.

SELECT CONVERT (DATETIME,  
CONVERT (DATE, DATEADD (DAY, -1 * (DAY(GETDATE()) - 1),
GETDATE())))

Change GETDATE() to the date you want

score 0 · Answer 24 · answered Nov 13 '15 at 02:32

0

I personal recommended that the sql below because when i try use date function in the condition clause, its slow down my query speed very much.

anyway feel free to try this.

select CONCAT(DATEPART(YYYY,@mydate),'-',DATEPART(MM,@mydate),'-01')

answered Nov 13 '15 at 02:32

Jelly

33
10

score 0 · Answer 25 · answered Aug 10 '16 at 01:47

0

Not to compete with any of the great minds here, but a simple suggestion slightly different that the accepted answer above.

select dateadd(day, -(datepart(day,@date)+1,@date)

answered Aug 10 '16 at 01:47

score 0 · Answer 26 · answered Oct 17 '18 at 09:21

0

I like to use FORMAT, you can even specify a time

SELECT FORMAT(@myDate,'yyyy-MM-01 06:00') first_of_a_month

answered Oct 17 '18 at 09:21

michal

319
3
15

score 0 · Answer 27 · answered Jan 04 '19 at 09:56

0

In Sql Server 2012,

 select getdate()-DATEPART(day, getdate())+1

 select DATEADD(Month,1,getdate())-DATEPART(day, getdate())

answered Jan 04 '19 at 09:56

BornToCode

199
1
7

score 0 · Answer 28 · answered Sep 09 '19 at 12:24

0

For anyone still looking for an answer, this works like a charm and does away with any dateadds. The timestamp is optional, in case it needs specifying, but works without as well.

SELECT left(convert(varchar, getdate(),23),7)+'-01 00:00:00'

answered Sep 09 '19 at 12:24

chimpSociety

23
6

score 0 · Answer 29 · answered Dec 27 '19 at 09:45

Get First Date and Last Date in the Date we pass as parameter in SQL

     @date DATETIME
    SELECT @date = GETDATE()
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),105) AS value,
    'First Day of Current Month' AS name
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),
    DATEADD(mm,1,@date)),105),
    'Last Day of Current Month'
    GO


      **OutPut**

12/01/2019  First Day of Current Month
12/31/2019  Last Day of Current Month

score -1 · Answer 30 · answered Aug 12 '20 at 23:07

What about something different! Use Format.

DECLARE @Date Date =GetDate();
SELECT CONVERT(Date,Format(@Date,'yyyyMM01'));

We can remove the convert if we are casting to Date Column or variable

DECLARE @Date Date =GetDate();
SELECT @Date =Format(@Date,'yyyyMM01');
SELECT [Date]=@Date

Have Fun :)

score -2 · Answer 31 · edited Apr 21 '21 at 11:52

-2

Try the following:

select trunc(sysdate,'MM') 
from dual;

edited Apr 21 '21 at 11:52

AirlineDog

522
7
19

answered Apr 21 '21 at 09:44

manoj

1

1

The question is about SQL Server, which does not have a `trunc` function. Always make sure you check the question tags. – Connor Low Apr 21 '21 at 15:47

score -2 · Answer 32 · edited Apr 21 '21 at 13:51

-2

Try the following:

select trunc(to_date('23-03-2021','DD-MM-YYYY'),'MM') from dual;

edited Apr 21 '21 at 13:51

Michal Levý

24,601
3
44
68

answered Apr 21 '21 at 09:48

manoj

1

jsarma · Answer 33 · 2020-03-11T14:12:30.953

-4

Here's how you'd do it in MySQL:

  select DATE_FORMAT(NOW(), '%Y-%m-1')

edited Mar 11 '20 at 14:12

answered Aug 14 '14 at 21:49

jsarma

1,284
11
16

4

Please specify that this is not sql-server answer but works only on MySQL. – kuklei Oct 17 '14 at 15:35
For SQL Server, you can use `SELECT FORMAT(GETDATE(), 'yyyy-MM-01')` – NASSER Oct 31 '18 at 12:25
Don't encourage people to format dates into strings just to do something that can and should be done numerically. It's like saying "you can round a number down to the nearest 100 by formatting it into a string, substringing all but the last two digits off it, and concatenating on a '00'" – Caius Jard Mar 09 '20 at 20:18

How can I select the first day of a month in SQL?

33 Answers33

Linked

Related