SQLTeam.com | Weblogs | Forums

DATEFROMPARTS is not a recognized built-in function name


#1

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]


#2

Please disregard this post.


#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

#4

Thank you.


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