CODEWITHSUNDEEP
sql-servert-sql
LonelyRogue
LonelyRogue

Reputation: 466

How to access SQL Server System Functions like CAST, CONVERT using 3 part naming covention

Like below, am trying to access System Function CAST() using Database name as prefix but isn't working.

SELECT Master.Dbo.CAST(GETDATE() AS DATETIME2),UserDB.dbo.CAST(GETDATE() AS DATETIME2)

Error : Incorrect syntax near the keyword 'AS'.

Replacing 'dbo' with 'sys' or '..' didn't work and it appears that CAST works stand-alone only without any prefix.

Those 2 Databases has different Compat Levels and so the output differs which is what I want to capture in one go.

Does someone know how to make it work, please?

Upvotes: 0

Views: 82

Answers (1)

Thom A
Thom A

Reputation: 95949

If you want to display how the calculations for a datetime to datetime2 differs, then you need to run the statements within the context of the 2 different databases with the different compatibility settings, like this:

CREATE DATABASE Test2019DB; --I am Running 2019 Preview Edition
GO

CREATE DATABASE Test2012DB;
GO

ALTER DATABASE Test2012DB SET COMPATIBILITY_LEVEL = 110;
GO

USE Test2019DB;
GO
SELECT CONVERT(datetime2(7),CONVERT(datetime,'2019-11-04T11:23:47.123')) AS DT2_2019;
GO

USE Test2012DB;
GO
SELECT CONVERT(datetime2(7),CONVERT(datetime,'2019-11-04T11:23:47.123')) AS DT2_2012;
GO

USE master;
GO

--clean up
DROP DATABASE Test2019DB;
DROP DATABASE Test2012DB;

Return values:

DT2_2019
2019-11-04 11:23:47.1233333 

DT2_2012
2019-11-04 11:23:47.1230000

Upvotes: 2

Related Questions