Why does LEFT (Datetime, 1) give me "J"?
So, I am writing a SQL query and it gives me a strange error:
Conversion error when converting varchar value 'J' to int datatype
I narrowed it down to LEFT(ProjApprovelDate,1)
which for some reason gives me J.
ProjApprovelDate is a DateTime most of the time, there are a few instances where it is typed incorrectly and is an int instead. To find them I used (LEFT(ap.ApprovalDate,1) != 1 and LEFT(ap.ApprovalDate,1) != 2)
. It always starts with 1 or 2 when it is in the wrong format. The entire column (in the original table) is int format and appears with most type dates 20170614
, but there are a few that appear as 1170614
. I am converting them to the correct format and inserting them all into a new table with this column as DateTime so that it enters them into the date correctly.
When browsing to make sure I got them all, I found this interesting case where those already formatted correctly like DateTime give me J.
So my question is, why does the first character of the LEFT
DateTime give J
for output?
source to share
The implicit conversion is a string, so ...
Select cast(getdate() as varchar(25))
,left(getdate(),1)
Returns
(No column name) (No column name)
Jun 14 2017 10:28AM J
Take a look at https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine
Just for fun try
Select left(25,1)
source to share