Hi,I have a date parameter (@rptMonth) that is selected by the user from a datepicker calendar. The date must be the first day of the month. No matter what the user selects I'd like to turn that into mm/01/yyyy. For example- I need the first day of the month. So if the user selects 06/22/2010, I need to turn that into 06/01/2010. So in my query it would be something like WHERE YEAR_MONTH = DATEADD("m",datediff("m","1900-01-01",@RptMonth),"1900-01-01"),"mm/dd/yyyy" but when I try this I get incorrect syntax near ','. Don't know if this will even work.
+2
A:
Try this:
declare @arbitraryDate datetime;
set @arbitraryDate = getdate();
set @arbitraryDate = dateadd(dd, datediff(dd, 0, @arbitraryDate), 0) --strip time
select dateadd(dd, -day(@arbitraryDate)+1,@arbitraryDate) --strip days
Or this:
select cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
Denis Valeev
2010-09-21 20:41:10
+2
A:
This should work too:
SELECT CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '01' AS DATETIME)
Abe Miessler
2010-09-21 20:54:42
I believe `2010/12/12` is culture-dependant. While `20101212` is not. I don't know why I know this.
Denis Valeev
2010-09-21 20:59:43
**Update**. `set dateformat ydm; select cast('2010/24/12' as datetime)` There you go! Ambiguity. But regardless the `dateformat` setting, this query `select cast('20101224' as datetime)` will work just fine.
Denis Valeev
2010-09-21 21:05:57
Your query also allows to easily go one month back or forth. Very useful! Definitely +1.
Denis Valeev
2010-09-21 21:07:48
Good to know, thanks for the followup!
Abe Miessler
2010-09-21 21:08:22