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 this is not very elegant  and probably not very fast either   Is there a better way to do this  I m using SQL Server 2008

User · Answer

Starting with SQL Server 2012   SELECT DATEADD DAY 1 EOMONTH  mydate -1

User · Answer

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

User · Answer

This works too       SELECT DATEADD DAY  DATEPART DAY  mydate -1   -1   mydate  AS FirstOfMonth

User · Answer

In Sql Server 2012    select getdate  -DATEPART day  getdate    1   select DATEADD Month 1 getdate   -DATEPART day  getdate

User · Answer

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

User · Answer

Try executing the following query   SELECT DATE ADD DATE ADD LAST DAY CURRENT DATE-INTERVAL 1 DAY  INTERVAL 1 DAY  INTERVAL -1 MONTH

User · Answer

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

User · Answer

select CONVERT date DATEADD dd - DATEPART dd getdate   -1  getdate    120   This function will provide you date part of start date of the month

User · Answer

I like to use FORMAT  you can even specify a time  SELECT FORMAT  myDate  yyyy-MM-01 06 00   first of a month

User · Answer

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

User · Answer

If using SQL Server 2012 or above   SELECT DATEADD MONTH  -1  DATEADD DAY  1  EOMONTH GETDATE

User · Answer

Future googlers  on MySQL  try this   select date sub ref date  interval day ref date -1 day  as day1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here s how you d do it in MySQL     select DATE FORMAT NOW      Y- m-1

User · Answer

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

User · Answer

In addition to all the above answer  a way based on a function introduced in sql 2012  SELECT DATEFROMPARTS YEAR  mydate  MONTH  mydate  1

User · Answer

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

User · Answer

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

User · Answer

First and last day of the current month   select dateadd mm  -1 dateadd dd   1  eomonth getdate      as FirstDay   eomonth getdate    as LastDay

User · Answer

SELECT  myDate - DAY  myDate    1

User · Answer

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

User · Answer

----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

User · Answer

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

User · Answer

This query should work very well on MySQL       SELECT concat left curdate   7   -01

User · Answer

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

User · Answer

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

User · Answer

SELECT DATEADD month  DATEDIFF month  0   mydate   0  AS StartOfMonth

User · Answer

Simple Query   SELECT DATEADD m  DATEDIFF m  0  GETDATE     0   -- Instead of GetDate you can put any date

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to datetime