I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.it's still the same old plus sign|||I figured out what I was doing wrong. I used text as my data type for the two fields I was trying to CONCAT. So when I did First_name + ' ' + Last_name is was treating the two columns as text and the blank space as a varchar. So i would execute.|||don't use the text type for such small fields as first and last names. text is for big blobs of text longer than 8000 chars. I don't know anyone with such a long name. :)
and in 2005, don't use text at all. use varchar(max) or nvarchar(max).
Showing posts with label clear. Show all posts
Showing posts with label clear. Show all posts
Tuesday, March 27, 2012
Saturday, February 25, 2012
Complex Join, I think.
I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.
customer
id | name
1 | fred
2 | tom
3 | eric
4 | fred
custrate
cust_id | rate_id
1 | 120
1 | 200
2 | 130
2 | 300
3 | 120
4 | 130
I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
inner
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
case when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null|||select * from customer c
where not exists
(select 1 from custrate r
where c.id=r.cust_id
group by r.cust_id
having count(*)%2=0 -- Must be paired
)
PS. There is something amiss with Rudy's query|||PS. There is something amiss with Rudy's queryindeed there was, 2 things amiss
try it now --select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
left outer
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null:)|||4:30?
As in AM?
you just getting in?|||Rudy has no need for sleep.
Sleep is inefficient.
:p|||sorry for the late reply, was out all day
yeah, i normally get up around 4:00 or 4:30 am
go to bed when it gets dark
:)|||sorry for the late reply, was out all day
yeah, i normally get up around 4:00 or 4:30 am
go to bed when it gets dark
:)
You must have a heck of a time during the summer when it doesn't get dark until after 10:00 PM ... or is it about 10:30 in your area?|||indeed, sometimes i get sleepy well before it gets dark!
customer
id | name
1 | fred
2 | tom
3 | eric
4 | fred
custrate
cust_id | rate_id
1 | 120
1 | 200
2 | 130
2 | 300
3 | 120
4 | 130
I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
inner
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
case when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null|||select * from customer c
where not exists
(select 1 from custrate r
where c.id=r.cust_id
group by r.cust_id
having count(*)%2=0 -- Must be paired
)
PS. There is something amiss with Rudy's query|||PS. There is something amiss with Rudy's queryindeed there was, 2 things amiss
try it now --select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
left outer
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null:)|||4:30?
As in AM?
you just getting in?|||Rudy has no need for sleep.
Sleep is inefficient.
:p|||sorry for the late reply, was out all day
yeah, i normally get up around 4:00 or 4:30 am
go to bed when it gets dark
:)|||sorry for the late reply, was out all day
yeah, i normally get up around 4:00 or 4:30 am
go to bed when it gets dark
:)
You must have a heck of a time during the summer when it doesn't get dark until after 10:00 PM ... or is it about 10:30 in your area?|||indeed, sometimes i get sleepy well before it gets dark!
Friday, February 24, 2012
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.
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.
Subscribe to:
Posts (Atom)