SQLTeam.com | Weblogs | Forums

Conversion error when using SELECT


#1

So, I'm getting a "Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string." when I run the following query. What's going on?

 select    
    sub.SSNO
    +REPLICATE (' ',714)
    +sub.BDFW
    +sub.BDFWDT
    +sub.ADM
    +sub.ADMDT
    +REPLICATE (' ',1431) 
    from 
    (select   e.SSNO as ssno,
    			BDFW='7153',
    		e.[SCH-BD-DT] as BDFWDT,
    		ADM='7154',
    		e.[SCH-ADM-DT] as ADMDT
    		
    		from Manual.dbo.External_Award_Dates_Prep e
    				) sub
 
      order by 1

The dates in the query are "BDFWDT" and "ADMDT" and are in the table as dates.


#2

Are you able to run the code below with no issue?

In essence you're just building a massive long string, have you tried converting all the elements into VARCHAR before you're concatenating them?


#3

The query you provided does work. I've not tried converting to VARCHAR. Would I need to convert each column, or just the dates?


#4

Start with the dates and if the issue persists, convert the other non string datatypes as well.


#5

How would I get it into MMDDYYYY format?

Using:

convert(varchar,e.[SCH-BD-DT], 101) as BDFWDT,

gets me MM/DD/YYYY, but I need it without the slashes.


#6

Nevermind, I used:

replace(convert(varchar,e.[SCH-BD-DT], 101), '/', '') as BDFWDT,