Complex Date Formatting

I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view?
Thanksselect cast(cast(nullif(@.IntDate, 0) as char(8)) as datetime)|||..or more robust (will return null without error if integer is not a valid date):

select cast(cast(nullif(@.IntDate*isdate(cast(nullif(@.IntD ate, 0) as char(8))), 0) as char(8)) as datetime)|||What do you mean by 2 views?

Can you post the code?|||I have one view that uses case to find the zeros and replace them with nulls. Then I have another view that uses convert to convert the not null values to the date string I want. I can't use the convert on the first view because it won't work on an integer string. In the second view I convert the non nulls to a char string and then to the date format I want. In other words I am using view on a view to do this. Thanks.|||Well...how about something like this...

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 0 UNION ALL
SELECT 20031231 UNION ALL
SELECT 20010101 UNION ALL
SELECT 19601024 UNION ALL
SELECT 11111111
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD DateComputed AS
CASE WHEN ISDATE(SUBSTRING(CONVERT(varchar(15),Col2),5,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),7,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),1,4))=1
THEN CONVERT(varchar(25),SUBSTRING(CONVERT(varchar(15), Col2),5,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),7,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),1,4),120)
ELSE NULL
END
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

But why not fix the datatype in the first place?|||Thanks for your reply. What do you mean by fixing the datatype in the first place? You mean to alter the table and change it from integer to char?|||Originally posted by exdter
Thanks for your reply. What do you mean by fixing the datatype in the first place? You mean to alter the table and change it from integer to char?

Integer to datetime...

But first you'll have to cleanup the mess....

Did you like the computed column solution?|||I can't alter the table because it is the backend to software we bought. They will not give support if we alter the tables. I have to work out the code you sent. I am not as advanced as you are (is anyone?) and need to sort out what it does and how to implement it. I thought about using the concatination but just wondered if there was a way to use char, case, and convert in the same line. I see your code alters the original table, which I can't do.
Thanks for your time.|||Yeah, just take the CASE Statement out of the ALTER

Should work as well in your view...

So you got a 3rd party product that stores dates as an int...

I love these guys...can you say who it is?

And if you need help with the view...post it...|||They are called SoftPro. http://www.softprocorp.com
Thanks.|||Hello! Anybody there? Did you TRY select cast(cast(nullif(@.IntDate, 0) as char(8)) as datetime)??|||Yes I tried it and when I try to open the view, my system locks up and I have to close the QA tool.|||Hello?

DECLARE @.IntDate int
SET @.IntDate = 20031231
select cast(cast(nullif(@.IntDate, 0) as char(8)) as datetime)
SET @.IntDate = 0
select cast(cast(nullif(@.IntDate, 0) as char(8)) as datetime)
SET @.IntDate = 11111111
select cast(cast(nullif(@.IntDate, 0) as char(8)) as datetime)|||I can use this in a view?|||Exdter:

Try this twist off blindman's code...

DECLARE @.IntDate int
SET @.IntDate = 20031231
SELECT CASE WHEN ISDATE(@.IntDate) = 1 THEN cast(cast(@.IntDate as char(8)) as datetime) ELSE NULL END
SET @.IntDate = 0
SELECT CASE WHEN ISDATE(@.IntDate) = 1 THEN cast(cast(@.IntDate as char(8)) as datetime) ELSE NULL END
SET @.IntDate = 11111111
SELECT CASE WHEN ISDATE(@.IntDate) = 1 THEN cast(cast(@.IntDate as char(8)) as datetime) ELSE NULL END

a lot cleaner than mine...for some reason I didn't think 20031231 would convert|||I don't understand where I can use this in my view. Sorry. I'm just learning.|||Something like:

CREATE VIEW myView99 AS
SELECT CASE WHEN ISDATE(IntDateCol) = 1 THEN cast(cast(@.IntDate as char(8)) as datetime)
ELSE NULL
END AS NewDateColumn
, Col2
, Col3
FROM yourTable99
GO

Post your code that creates the view....|||ALTER VIEW dbo.Settlement_Status
AS
SELECT dbo.MiscText2.FirmFile, dbo.MiscText2.FirmCode, dbo.Search.Closer,case settdate when 0 then null else settdate end settdate, dbo.DatesTimes.SettTime, dbo.misctext1.Byrbrief,
dbo.Search.SlrName, dbo.Search.Ag701Nam, dbo.Search.Ag701Frm, dbo.Search.Ag702Nam, dbo.Search.Ag702Frm, dbo.Search.LenName,
dbo.MiscText1.LoanOfcr,
case what when 'Tax Info Obtained' then cpdt else null end 'Tax_Info_Obtained',
case what when 'HOA dues' then cpdt else null end 'HOA_dues',
case what when 'Cert. of Insurance Requested' then cpdt else null end 'Cert_of_Insurance_Requested',
case what when 'Estimated HUD sent out' then cpdt else null end 'Estimated_HUD_sent_out'
FROM dbo.MiscText2 INNER JOIN
dbo.MiscText1 ON dbo.MiscText2.FirmFile = dbo.MiscText1.FirmFile INNER JOIN
dbo.Search ON dbo.MiscText2.FirmFile = dbo.Search.FirmFile INNER JOIN
dbo.TrackItems ON dbo.MiscText2.FirmFile = dbo.TrackItems.FirmFile INNER JOIN
dbo.DatesTimes ON dbo.MiscText2.FirmFile = dbo.DatesTimes.FirmFile
WHERE (dbo.TrackItems.What IN ('HOA dues', 'Cert. of Insurance Requested', 'Tax Info Obtained', 'Estimated HUD sent out'))
and substring(dbo.MiscText2.firmfile,1,2) between '01' and '21'|||If there is a cpdt (completed date) then I want to put it in the corresponding "what" column. I hope you can make heads or tails out of this.|||I am looking for something like what you posted for someone else just now.
CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate
This just locks up my system.|||Originally posted by Brett Kaiser
Something like:

CREATE VIEW myView99 AS
SELECT CASE WHEN ISDATE(IntDateCol) = 1 THEN cast(cast(@.IntDate as char(8)) as datetime)
ELSE NULL
END AS NewDateColumn
, Col2
, Col3
FROM yourTable99
GO

Post your code that creates the view....

I did it this way and that is what I was looking for. Thanks for your patience.|||Does you r view actually get created?

This block doesn't make sense..

case what when 'Tax Info Obtained' then cpdt else null end 'Tax_Info_Obtained',
case what when 'HOA dues' then cpdt else null end 'HOA_dues',
case what when 'Cert. of Insurance Requested' then cpdt else null end 'Cert_of_Insurance_Requested',
case what when 'Estimated HUD sent out' then cpdt else null end 'Estimated_HUD_sent_out'|||You're right about that. It gets created but those lines are redundant. I did this and got exactly what I wanted.

create view dbo.my_view99
AS
SELECT FirmFile,
case when isdate(closdate)=1 then convert(char(10),convert(datetime,cast(closdate as varchar(8))),101)
else null end closdate,
case when isdate(DueDate)=1 then cast(cast(DueDate as char(8)) as datetime)
else null end DueDate

FROM dbo.DatesTimes

You are a genius. Thanks so much.|||OK...I don't pretend to understand some of what you're doing...

but how does this look

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Settlement_Status]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[Settlement_Status]
GO

CREATE VIEW dbo.Settlement_Status
AS
SELECT dbo.MiscText2.FirmFile
, dbo.MiscText2.FirmCode
, dbo.Search.Closer
-- I'm guessing this is the integer column
, CASE WHEN ISDATE(settdate) = 1 THEN cast(cast(settdate as char(8)) as datetime)
ELSE NULL
END AS SetDate
-- Original Code
-- , case settdate when 0 then null else settdate end settdate
, dbo.DatesTimes.SettTime
, dbo.misctext1.Byrbrief
, dbo.Search.SlrName
, dbo.Search.Ag701Nam
, dbo.Search.Ag701Frm
, dbo.Search.Ag702Nam
, dbo.Search.Ag702Frm
, dbo.Search.LenName
, dbo.MiscText1.LoanOfcr
-- Since this is part of your WHERE Clause, won't it always be true?
, CASE WHEN WHAT IN ( 'Tax Info Obtained'
, 'HOA dues'
, 'Cert. of Insurance Requested'
, 'Estimated HUD sent out')
THEN cpdt
ELSE NULL AS cpdt
END
-- Holy Join batman
FROM dbo.MiscText2
INNER JOIN dbo.MiscText1
ON dbo.MiscText2.FirmFile = dbo.MiscText1.FirmFile
INNER JOIN dbo.Search
ON dbo.MiscText2.FirmFile = dbo.Search.FirmFile
INNER JOIN dbo.TrackItems
ON dbo.MiscText2.FirmFile = dbo.TrackItems.FirmFile
INNER JOIN dbo.DatesTimes
ON dbo.MiscText2.FirmFile = dbo.DatesTimes.FirmFile
WHERE (dbo.TrackItems.What IN ('HOA dues', 'Cert. of Insurance Requested', 'Tax Info Obtained', 'Estimated HUD sent out'))
AND SUBSTRING(dbo.MiscText2.firmfile,1,2) between '01' and '21'
GO|||Originally posted by exdter
You are a genius. Thanks so much.

Hey watch it...you're gonna start a flame war...8-)|||You don't have to rack your brain anymore. I got what I wanted with:

case when isdate(closdate)=1 then convert(char(10),convert(datetime,cast(closdate as varchar(8))),101)
else null end closdate

Thanks.|||Originally posted by Brett Kaiser
Hey watch it...you're gonna start a flame war...8-)

Sorry I'm a bit slow, but what's a flame war? Is it like a Jihad?|||Originally posted by Brett Kaiser
Yeah, just take the CASE Statement out of the ALTER

Should work as well in your view...

So you got a 3rd party product that stores dates as an int...

I love these guys...can you say who it is?

And if you need help with the view...post it...

Third party, heck. MS themselves do it in the system tables in msdb relating to SQL Agent Jobs. ARRRGGH I HATE THAT.|||Originally posted by Steve Duncan
Third party, heck. MS themselves do it in the system tables in msdb relating to SQL Agent Jobs. ARRRGGH I HATE THAT.

At least it forces me to learn.|||Originally posted by exdter
Sorry I'm a bit slow, but what's a flame war? Is it like a Jihad?

Ohh..it's when (some) people get stupid and post nasty comments back and forth...goes on for days sometimes...

For some reason (some) people actually think it matters...some times it's just down right funny...

Sometimes it's just down right rude...

some things (some) people type, they would never say to someone face to face...

Hey ...GOOD LUCK!|||Thanks again, maybe I can help you out someday (yeah right). The isdate function was what I was missing.