Menu
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 unfortunately, this is not very elegant, and not very fast either.
Is there a better way to do this? I'm using SQL Server 2008.
276
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.
SQL Server EOMONTH() overview The EOMONTH() function returns the last day of the month of a specified date, with an optional offset. The EOMONTH() function accepts two arguments: start_date is a date expression that evaluates to a date.
Syntax : = EOMONTH(start_date, months) If we set parameter months as 0, then EOMONTH will output the last day of the month in which StartDate falls.
SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
