[sql] 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

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)

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) 

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)

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

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

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)

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

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

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

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

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

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)

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