Why CAST from Date time to varchar works in one Environment but not the other?

For this questions, we have two environments, Dev and Prod with both running sql2014 with dev being a developers version and prod an enterprise version. Dev is running the below version(s)

Prod is running the below version(s)

Both are using the same data set (static data set from prod. DB was restored from Prod to Dev today), but when casting from Date/time to Varchar in Dev, its throws a conversation error, but in our Prod environment, it works just fine.

Could the difference in version we see here be the reason it doesnt work in our lower environment.

Here is the Dev Exact version #: SELECT @@Version

Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) 
Sep 23 2016 18:13:56 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Prod Version

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
Jun 17 2016 19:14:09 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Here is my SQL, its apart of a CTE Update statement if wondering what the hell im doing:

 SELECT W.[order number], W.[BO NUMBER]
,CAST(MAX(cast(CT.[trans-date] as varchar) + ' ' + CT.[CHAR-OLD]) as date) AS 'TDate'
,SUBSTRING(cast(CAST(MAX(cast(CT.[trans-date] as varchar) + ' ' + CT.[CHAR-OLD]) as time(0))as varchar),1,5) as 'TTime'

FROM [dbo].[PURCHASE_TO_PORCH_WORK] W

            inner join dbo.[change_tracking] ct
            on W.[ORDER NUMBER]= ct.[document-num]
            and ct.[bo-number] = W.[BO NUMBER]

WHERE ct.[change-id] = 'WTRCV'
and W.[ACTION CODE] = 'SI'
group by [ORDER NUMBER], [BO NUMBER]

Date Types (Both Env):

[trans-date] = (date, null)

[char-old] = (varchar(16), null)

Answer

Issue was actually DB compatibility level. Supposed to be set to 2008 (100) but was 2014 (120). i also go it working in 2014 (120) compatibility by not casting CT.[trans-date] as varchar. Even though that answer wasnt provided, the suggestion helped me trouble shoot so much appreciated!

Advertisement

Answer