Need Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help

Author

Topic

michpaus
Starting Member

10 Posts

Posted - 2011-09-26 : 13:56:01

I have a query. I am pulling from a table that has a record with a null value in the high_school_grad_date field and the query fails on this error "Conversion failed when converting date and/or time from character string". I thought putting the isnull function would take care of it. Can someone take a look below and tell me what I am doing wrong? Appreciate any feedback

SELECT
c1.SATIdentity,
LEFT(ISNULL(email_address, c1.first_name + '.'+ c1.last_name +'@noemail.com'),255) as email_address, -- create email if none is existent
s1.EssayLocatorID,
s1.TestDate,
s1.sat_reading,
s1.sat_writing,
s1.sat_math,
(convert(datetime,left(isnull(c1.high_school_grad_date,2),00) + '/01/' + right(isnull(c1.high_school_grad_date,2),00)))as high_school_grad_date,
c1.ceeb,
c1.high_school_name
FROM [tlImport].[dbo].[CC_SatScores_Pre] c1
inner join @mytable s1 on s1.SATIdentity = c1.SATIdentity
WHERE (c1.talismasatimport = 0 and c1.talismaimport = 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:02:45

no need of all these convert and concatenate. i hope your attempt is to get first date of month. then its enough to do like


SELECT 
c1.SATIdentity,
LEFT(ISNULL(email_address, c1.first_name + '.'+ c1.last_name +'@noemail.com'),255) as email_address, -- create email if none is existent
s1.EssayLocatorID,
s1.TestDate,
s1.sat_reading,
s1.sat_writing,
s1.sat_math,
dateadd(mm,datediff(mm,0,c1.high_school_grad_date),0)as high_school_grad_date,
c1.ceeb,
c1.high_school_name
FROM [tlImport].[dbo].[CC_SatScores_Pre] c1
inner join @mytable s1 on s1.SATIdentity = c1.SATIdentity
WHERE (c1.talismasatimport = 0 and c1.talismaimport = 1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michpaus
Starting Member

10 Posts

Posted - 2011-09-26 : 14:12:07

I have to do the convert. I am pulling from a flat file that I dump into the CC_SatScores_Pre table and the date field comes in as a text for example 0611 or a mmyy format. I am trying to convert it to a mm/dd/yy format and take into account that there may be instences where the field is null. Just wondering if it can be done and in what way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:20:34

then do like

case when c1.high_school_grad_date is null then cast(null as datetime) else convert(datetime,stuff(c1.high_school_grad_date,3,0,'/01/'),1) end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources