DATEFROMPARTS is not a recognized built-in function name

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.

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

Thank you.

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 :slight_smile: