Hakan Axheim

Hakan Axheim

  • 1.8k
  • 100
  • 4.4k

Convert of NVARCHAR and DATETIME

Nov 19 2018 8:36 AM
Hi,

I have to columns that I want to concat and try make a test in the SQL query clause.
The first column is an VARCHAR and the other column is a DATETIME.
 
I have written an SQL shown below:
 
As you see I have a statement CONCAT(w.waabbr,TRY_CONVERT(VARCHAR(35),w.wabeginning,121)) AS tst
 that will be correct if I run the sql without the statement in the where clause, but if I insert the statement in the where clause I got a warning Type conversion in expression (TRY_CONVERT(varchar(35),[w].[wabeginning],121)) may affect "CardinalityEstimate" in query plan choice; Type conversion in expression (CONVERT(varchar(23),[w].[wabeginning],121)) may affect "CardinalityEstimate" in query plan choice
 
 
SELECT r.roward + '¤' + r.roidnr as id,
r.roidnr as id2,
CASE
WHEN (CURRENT_TIMESTAMPr.roend)
THEN 'inactive'
WHEN (CURRENT_TIMESTAMP>=r.robeginning) and (r.roend is not null or CURRENT_TIMESTAMP<=r.roend)
THEN 'suspended'
ELSE 'active'
END AS status,
r.roname AS name,
'instance' as mode,
r.roidnr as typeCode, -- temporary fix
'' as typeDisplay,
r.rophone as telecom,
'' as Address,
'ro' as physicalType,
w.waabbr + CONVERT(varchar(23), w.wabeginning, 121) as partof,
r.rofloor as rofloor,
CASE
WHEN r.rotype = 'N'
THEN 'female'
WHEN r.rotype = 'M'
THEN 'male'
ELSE 'mixed'
END as rotype,
'' as closedReason,
'false' as overPlace,
CONCAT(w.waabbr,TRY_CONVERT(VARCHAR(35),w.wabeginning,121)) AS tst,
TRY_CAST(w.wabeginning AS DATETIME) AS tst2
from [EfficaDefault].[dbo].wd_room as r
INNER JOIN [EfficaDefault].[dbo].wd_ward as w ON (r.roward = w.waabbr)
where r.roward = w.waabbr
-- AND CONCAT(w.waabbr,TRY_CONVERT(VARCHAR(35),w.wabeginning,121)) = '317'

Answers (3)