January 22, 2009 at 11:48 am
JPLeBlanc (1/22/2009)
Jeff,I beleive that would be two months since it is calculating it in days correct?
so not just 1 calendar months difference. or am i backward? 🙂
Lynn is correct... just 1... it only counts the "borders" of months... so using the dates of 2009-01-31 and 2009-02-01 would also give you "1".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2009 at 11:50 am
Also, be real careful with ISDATE... the following will return a "1" as will many other things...
SELECT ISDATE('2008')
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2009 at 11:55 am
Jeff Moden (1/22/2009)
Also, be real careful with ISDATE... the following will return a "1" as will many other things...SELECT ISDATE('2008')
So will most numbers. If you have to protect against other factors, like having 2008 in the data, you need to do more tests.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 1:37 pm
:unsure: :Whistling:
thanks guys, on the same note..
If data such as you suggest would return an error, then how could i go about preventing it without explicitly stating the possible errors? Put in a specified size amount of characters to look for as well as saying that it should begin with a 1 or a 2 for 1900 or 2000?
Also, since i am getting so much attention in this thread 🙂 (please let me know if i'm overstepping my bounds). I am having another issue with an update / join.
UPDATE dbo.[PCS Data – Details]
SET dbo.[PCS Data – Details].[timestamp] = dbo.[PCS data – Details Temp].[timstamp]
FROM dbo.[PCS Data – Details] INNER JOIN
dbo.[PCS Data – Details Temp] ON dbo.[PCS Data – Details].[uniquekey] = dbo.[PCS Data – Details Temp].[uniquekey]
what i am trying to accomplish here is to take the data from the new temp table and update the regular table with the updated information as it is changed daily. the error i receive is...
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.PCS Data – Details'.
To me.. i shouldnt receive this error unless a field or a table doesnt exist, right?
but i have verified that both tables and fields do exist. I have included a screenshot in the post to show you what i see.
sorry guys please don't make too much fun of my naming conventions i know i shouldnt uses spaces and dashes but i learned all of this after i really started using SQL.
January 22, 2009 at 2:23 pm
I don't see a temp table in that query. They should start with # on the name. The table with "Temp" in the table name is defined as a real table in the query by having dbo. in front of the name, braces around the name, and no # symbol at the beginning of the name. That tells SQL that it's a real table, and it's telling you it can't find that table.
On the date thing, it would depend on what you would consider "real dates". You could do something like:
select
case
when IsDate(MyColumn)=1 then
case
when cast(MyColumn as datetime) >= '1/1/2000' then cast(MyColumn as smalldatetime)
else null
end
else null
end
from dbo.MyTable
You can nest Case statements that way. It's not the best way to write code, because it can hurt performance, but in the case where you have junk data in a column, and you can't just delete the junk data, it can be necessary to sacrifice performance for accuracy sometimes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 2:50 pm
Awesome, i see where you are going from there, just a quick check that after the conversion is performed, that it is greater than the lowest date possible.
Soif '2007' is converted then it will check against 1/1/2008 and return an error, or in this case ignore it.
but what if it is '2009'? would it be converted into 1/1/2009? and since the statement is => 1/1/2008 then it would pass and give bad calculation?
on the join, yes both tables are REAL tables, like i said.. naming convention is horrible, but i made it and there is no time to start changing... and of course i am expounding the issues in the future by continuing to wkr this way 🙂 I'll bug you all about it later hehe.
i am still kind of confused then, they both exist, and the brackets should be taking care of the issue that they are real tables...
Brain asplodes!
(beleive me i am very thankful you all are being so patient with me 🙂 )
January 22, 2009 at 4:09 pm
JPLeBlanc (1/22/2009)
UPDATE dbo.[PCS Data – Details]SET dbo.[PCS Data – Details].[timestamp] = dbo.[PCS data – Details Temp].[timstamp]
FROM dbo.[PCS Data – Details] INNER JOIN
dbo.[PCS Data – Details Temp] ON dbo.[PCS Data – Details].[uniquekey] = dbo.[PCS Data – Details Temp].[uniquekey][/code]
got this working..
I tend to use Word as an editor when i am adding code that repeats itself with only minor changes. as i can copy and paste and easily see everything i need, i know instead of getting rid of the object explorer....
but i digress.
I was able to repair this by clicking on the table names from the object explorer and copying into the code. apparently a - in SQL code editor is not the same as a - in word. i guess it would be something to do with fonts but who knows.
edit: here is the final code that works, as you can see i got really distressed and started using aliases...
UPDATE [ac]
SET [timestamp] = bc.[timestamp]
FROM [PCS Data - Details Temp] as bc
INNER
JOIN [PCS Data - Details] as [ac]
ON bc.[uniquekey] = [ac].[uniquekey]
January 23, 2009 at 7:21 am
JPLeBlanc (1/22/2009)
Awesome, i see where you are going from there, just a quick check that after the conversion is performed, that it is greater than the lowest date possible.Soif '2007' is converted then it will check against 1/1/2008 and return an error, or in this case ignore it.
but what if it is '2009'? would it be converted into 1/1/2009? and since the statement is => 1/1/2008 then it would pass and give bad calculation?
on the join, yes both tables are REAL tables, like i said.. naming convention is horrible, but i made it and there is no time to start changing... and of course i am expounding the issues in the future by continuing to wkr this way 🙂 I'll bug you all about it later hehe.
i am still kind of confused then, they both exist, and the brackets should be taking care of the issue that they are real tables...
Brain asplodes!
(beleive me i am very thankful you all are being so patient with me 🙂 )
It will convert those to years. You'll have to work out what rules will work for you, based on your particular situation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 7:22 am
On the point of both being real tables, the error from the code is because the table doesn't exist at the time the code is being compiled. The table has to already exist.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply