Blasted NULLs. Cant figure out how to replace them

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

SQL Server 2008 Forums

Transact-SQL (2008)

Blasted NULLs. Cant figure out how to replace them

Author

Topic

Biscuithead
Starting Member

30 Posts

Posted - 2009-07-17 : 18:14:17

Here is my code:


SELECT     TOP (100) PERCENT dbo.VW_InOutAll.LASTNAME AS [Last Name], dbo.VW_InOutAll.FIRSTNAME AS [First Name], dbo.VW_InOutAll.Status AS [Current Status], 
                      dbo.VW_InOutAll.EVENTIME AS [Swipe Time], SUBSTRING(CONVERT(VARCHAR(20), dbo.VW_InOut_FI.EVENTIME, 100), 13, 7) AS [Start Time], 
                      SUBSTRING(CONVERT(VARCHAR(20), dbo.VW_InOut_LO.EVENTIME, 100), 13, 7) AS [Last Out], dbo.VW_InOutAll.EXT
FROM         dbo.VW_InOutAll INNER JOIN
                      dbo.VW_InOut_FI ON dbo.VW_InOutAll.EMPID = dbo.VW_InOut_FI.EMPID LEFT OUTER JOIN
                      dbo.VW_InOut_LO ON dbo.VW_InOutAll.EMPID = dbo.VW_InOut_LO.EMPID

I want to replace nulls found in the [Last Out] column with 00:00. But I cant figure out how to do it. My closest attempt creates another column with 00:00's in the same row as the [Last Out] columns nulls.
It seems simple, prob is, but its eluding me.
Any help is appreciated.
TIA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-17 : 18:36:53

Do you have tried:
isnull(SUBSTRING(CONVERT(VARCHAR(20), dbo.VW_InOut_LO.EVENTIME, 100), 13, 7),'00:00') AS [Last Out]

No, you're never too old to Yak'n'Roll if you're too young to die.