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

Question

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

Answer 1

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)

Answer 2

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) 

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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