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

sql sql-server tsql sql-server-2008 datetime
Brann picture Brann · Oct 5, 2009 · Viewed 872.5k times · Source

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.

Answer

LukeH picture LukeH · Oct 5, 2009 
SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More
Select statement to find duplicates on certain fields

Can you help me with SQL statements to find duplicates on multiple fields? For example, in pseudo code: select count(field1,field2,field3) from table where the combination of field1, field2, field3 occurs multiple times and from the above statement …

Read More