Convert of NVARCHAR and DATETIME

Hakan Axheim

7y3.5k

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'

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rupesh Kahane6y ago
Reger links it might be help you
1) https://www.sqlshack.com/sql-convert-date/
2) https://stackoverflow.com/questions/19218982/convert-nvarchar-to-datetime-in-sql-server-2008
3) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47fc07d2-37fe-4dd8-b57f-3867cd57e2b0/how-to-convert-nvarchar-to-datetime-datatype?forum=transactsql
0
Mageshwaran R6y ago
Hi,
please refer this link:
https://www.experts-exchange.com/questions/29056419/SQL-Convert-nvarchar-to-datetime-MM-DD-YYYY-format.html
0
Manav Pandya7y ago
Hello

You can follow few links :

https://stackoverflow.com/questions/19218982/convert-nvarchar-to-datetime-in-sql-server-2008

https://www.sqlservercentral.com/Forums/1248252/How-do-i-convert-nvarchar-to-datetime

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47fc07d2-37fe-4dd8-b57f-3867cd57e2b0/how-to-convert-nvarchar-to-datetime-datatype?forum=transactsql

https://www.experts-exchange.com/questions/29056419/SQL-Convert-nvarchar-to-datetime-MM-DD-YYYY-format.html

Thanks
0