Mike,

Thanks. I have now got around to trying this but on running the query it had the following errors -

Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 24 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 25 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 26 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 29 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 30 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 31 [Batch Start Line 7]
'trim' is not a recognized built-in function name.
Msg 15151, Level 16, State 1, Line 62
Cannot find the object 'LCReturnDCDate', because it does not exist or you do not have permission.

(0 rows affected)

(0 rows affected)

(1 row affected)

(1 row affected)

Completion time: 2025-02-10T11:52:59.9918997+00:00

The option to choose the Sample Darwin Core Date then does come up in the Report Wizard in Recorder but when selected there is an error which says 'Cannot find either column "dbo" or the user-defined function or aggregate "dbo.LCReturnDCDate", or the name is ambiguous.'

Phil

Hi Mike,

Yes I think it is SQL Server 2016. I am using the most up to date version of Management Studio though. I could try and amend your code but it is probably safer if you do. Also as it has now already put a 'Sample Darwin Core Date' in the report wizard (which does not work) I am concerned that putting another one in might cause a conflict, so might need another change in the code so it doesn't? (Perhaps just change the name of it?).

Thanks,

Phil

Hi Mike,

I have tried this now and there is an error in one line where 'Lrim' should be 'Ltrim'. See below -

Msg 195, Level 15, State 10, Procedure LCReturnDCDate, Line 30 [Batch Start Line 7]
'Lrim' is not a recognized built-in function name.

I corrected this myself and ran again and it works now.

However, I did an export with a variety of different date types after this and there was one record with a date range -

'21/07/2013 - 24/07/2013'

This came out as '2013-07--21/24'

But should be '2013-07-21/24' I think (with one -) looking at the NBN Atlas upload template guide.

Could you correct the above in the script and post again please?

Thanks,

Phil