Hi, I'm getting an error using this function. i'm using 2008 R2. Is there any alternative approach in getting the result. thank you.
please see below sample. let say I have a column name po_month the format is year and month with nvarchar datatype.
[code]declare @po_month nvarchar(7)
set @po_month='2016-09'
DATEFROMPARTS(CAST(SUBSTRING(po_month,1,4) AS INT), CAST(SUBSTRING(po_month,6,2) AS INT)-1,01) AS po_month_before
DATEFROMPARTS(CAST(SUBSTRING(po_month,1,4) AS INT), CAST(SUBSTRING(po_month,6,2) AS INT),01) AS po_month_curr[/code]
Please disregard this post.
bitsmed
September 7, 2016, 3:15pm
3
Well others might want to know.
Function datefromparts was first introduced in MSSQL 2012, and since you're on 2008, you get the error.
But you can do:
declare @po_month nvarchar(7)='2016-09';
select dateadd(day,-1,cast(@po_month+'-01' as date)) as po_month_before
,cast(@po_month+'-01' as date) as po_month_curr
Kristen
September 8, 2016, 2:12pm
5
personally I wouldn't use DATEFROMPARTS() for that job anyway, as it involves two SUBSTRINGS and CASTS for each call to DATEFROMPARTS().
The string-to-date that @bitsmed described should be more efficient.
Its not going to make any difference, though, unless a lot of rows are being processed / instances are being executed