CODEWITHSUNDEEP
sqlsql-serverdatetimedate
DaveDev
DaveDev

Reputation: 42175

How to get date representing the first day of a month?

I need functionality in a script that will enable me to insert dates into a table.

What SQL do I need to insert a date of the format

01/08/2010 00:00:00

where the date is the first day of the current month. What do I need to change order that I can specify the month value? Thanks

Upvotes: 38

Views: 165864

Answers (15)

Wil Gaboury
Wil Gaboury

Reputation: 91

Using the DATETRUNC function seems like by far the simplest way.

SELECT DATETRUNC(month, GETDATE())

Upvotes: 1

Nicholas Thomson
Nicholas Thomson

Reputation: 11

Get First Day of Last Month

Select ADDDATE(LAST_DAY(ADDDATE(now(), INTERVAL -2 MONTH)), INTERVAL 1 DAY);

Get Last Day of Last Month

Select LAST_DAY(ADDDATE(now(), INTERVAL -1 MONTH));

Upvotes: 0

John Smith
John Smith

Reputation: 7407

Here is a very simple way to do this (using SQL 2012 or later)

datefromparts(year(getdate()),month(getdate()),1)

you can also easily get the last day of the month using 

eomonth(getdate())

Upvotes: 1

Ian Kemp
Ian Kemp

Reputation: 29849

As of SQL Server 2012 you can use the eomonth built-in function, which is intended for getting the end of the month but can also be used to get the start as so:

select dateadd(day, 1, eomonth(<date>, -1))

If you need the result as a datetime etc., just cast it:

select cast(dateadd(day, 1, eomonth(<date>, -1)) as datetime)

Upvotes: 0

jmoreno
jmoreno

Reputation: 13551

The accepted answer works, and may be faster, but SQL 2012 and above have a more easily understood method:

SELECT cast(format(GETDATE(), 'yyyy-MM-01') as Date)

Upvotes: 10

Todd181
Todd181

Reputation: 31

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

This worked perfectly. I actually added a Case statement. Thanks for the post:

SELECT Case(DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) as Date)

Upvotes: 0

Lokesh BM
Lokesh BM

Reputation: 7

select last_day(add_months(sysdate,-1))+1 from dual;

Upvotes: -1

Icemanind
Icemanind

Reputation: 48686

The best and easiest way to do this is to use:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

Just replace GETDATE() with whatever date you need.

Upvotes: 99

Pod
Pod

Reputation: 1

Very simple piece of code to do this

$date - extract(day from $date) + 1

two ways to do this:

  1. my_date - extract(day from my_date) + 1

  2. '2014/01/21' - extract(day from '2014/01/21') + 1

Upvotes: -2

Jan
Jan

Reputation: 1

... in Powershell you can do something like this:

Get-Date (get-Date ((Get-Date) ) -format MM.yyyy)

... for the last month do this:

Get-Date (get-Date ((Get-Date).AddMonths(-1) ) -format MM.yyyy)

... or for custom Date do this:

Get-Date (get-Date ((Get-Date 12.01.2013) ) -format MM.yyyy)

Im sure theres something like this possible ...

Gruß

Upvotes: -2

Beth
Beth

Reputation: 9607

SELECT DATEADD(day,1-DATEpart(day, GETDATE()),GETDATE())

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

select cast(cast(datepart(year,getdate()) as char(4)) 
+ '/' 
+ cast(datepart(month,getdate()) as char(2))
+ '/01' as datetime)

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425271

SELECT  CAST(FLOOR(CAST(DATEADD(d, 1 - DAY(GETDATE()), GETDATE()) AS FLOAT)) AS DATETIME)

Upvotes: 0

AllenG
AllenG

Reputation: 8190

Modified from this link. This will return as string, but you can modify as needed to return your datetime data type.

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GetDate())-1),GetDate()),101)

Upvotes: 0

BlackICE
BlackICE

Reputation: 8916

i think normally converts string to MM/DD/YY HH:mm:ss, you would need to use 08/01/2010 00:00:00

Sorry, misunderstood the question, looking to see if you can change the order for strings.

This may be what you want:

declare @test as date

select @test = CONVERT(date, '01/08/2010 00:00:00', 103)
select convert(varchar(15), @test, 106)

Upvotes: 0

Related Questions