CODEWITHSUNDEEP
sql-serverssms
Jin Tan
Jin Tan

Reputation: 608

Unreadable character in SSMS

I need help in this.

When I query data from SSMS, I am getting weird character as shown in picture.

enter image description here

I have tried this SQL command, but it does not help. The desired result should be 1D or 1M or 1W.

CONVERT(varchar(50),[Date Formula 1]) COLLATE SQL_Latin1_General_CP1_CI_AS [Date Fromula 1]

Upvotes: 0

Views: 522

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 13009

It is not due to SSMS. It is due to the way data is stored.

Originally, when the data came as 1D/1M/1W, it did not get stored due to one of the reasons:

  • the default collation of the database did not support, ASCII characters like D/M/W characters
  • If the default collation is non-ASCII and the column datatype is not NVARCHAR or NCHAR and datatype you did not insert with N'1M', N'1D', N'1W' i.e., with N prefix
INSERT INTO TableName(DateFormula) VALUES (N'1M'),(N'1D'), (N'1W')
  • There is some issue in the Data access layer, where the data is not properly being passed to database

What can you do now? The data is lost while insertion. No way to recover it. Better re-insert the data with proper unicode datatype or with proper collation defined for the column.

Upvotes: 1

Related Questions