December 1, 2011 at 6:09 am
hi
can someone tell me why
select *
from
(
select '01-jan-1900' as DayBase
union select GETDATE() as DayBase
) a
where isnull(a.DayBase,'')<>''
gives one row?
thanks!
December 1, 2011 at 6:25 am
Because your WHERE clause is implicitly converting an empty string to a datetime.
e.g.
SELECT CAST('' AS DATETIME)
Which equals "1900-01-01 00:00:00.000".
So your WHERE clause is basically as follows: -
WHERE ISNULL(a.DayBase,'') <> '1900-01-01 00:00:00'
Which eliminates the "SELECT '01-jan-1900' AS DayBase" part of your query.
December 1, 2011 at 6:26 am
Here's a hint!
SELECT CONVERT(DATETIME, '')
December 1, 2011 at 6:53 am
aaaaaaaaaaaaaaah
brilliant
memo to self...use "IS NULL" not "ISNULL"
thanks guys
December 1, 2011 at 6:56 am
Not too sure what you need to do...
this will pretty much never filter out anything.
select *
from
(
select '01-jan-1900' as DayBase
union select GETDATE() as DayBase
) a
where a.DayBase IS NOT NULL
December 1, 2011 at 6:56 am
darth_vodka (12/1/2011)
aaaaaaaaaaaaaaahbrilliant
memo to self...use "IS NULL" not "ISNULL"
thanks guys
It has nothing to do with ISNULL. It has to do with the implicit conversion of the empty string into a DATETIME.
You basically wrote this: -
SELECT *
FROM (
SELECT '1900-01-01 00:00:00' AS DayBase
UNION
SELECT GETDATE() AS DayBase
) a
WHERE ISNULL(a.DayBase, '1900-01-01 00:00:00') <> '1900-01-01 00:00:00'
Which with your data-set, was in fact this -
SELECT *
FROM (
SELECT '1900-01-01 00:00:00' AS DayBase
UNION
SELECT GETDATE() AS DayBase
) a
WHERE a.DayBase <> '1900-01-01 00:00:00'
December 1, 2011 at 11:20 am
Hey Joe, you could at least "correct" people with the correct queries.
t'was 1900-01-01 NOT 03
December 1, 2011 at 11:24 am
CELKO (12/1/2011)
I am amazed at the dialect you got into one query! Here is how to actually write this in T-SQL using real ANSI SQL instead.SELECT X.*
FROM (VALUES (CAST ('1900-01-03' AS DATE)),
(CURRENT_TIMESTAMP)) AS X(something_date)
WHERE COALESCE (something_date,'') <> '';
When were derived tables removed from the ANSI standard for SQL?????? Or are you just trying to show off by over-complicating a simple task for no reason at all?
- 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
December 1, 2011 at 12:22 pm
CELKO (12/1/2011)
I am amazed at the dialect you got into one query! Here is how to actually write this in T-SQL using real ANSI SQL instead.
In case you hadn't noticed, this forum is dedicated to a specific dialect of SQL, namely T-SQL 2008. Are you also amazed when people in Boston speak with a Boston accent or people in the South speak with a southern drawl?
Just saying that something is not ANSI-compliant is simply not a compelling argument for most people. Most people are concerned first with getting it to work at all and then with getting it to work efficiently. Making sure that it is ANSI-compliant is far down on most people's lists, particularly when the dialect provides a solution that is easier to code and/or more efficient than the ANSI-compliant approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 1, 2011 at 6:02 pm
100% OFF the list is far more accurate for the majority of folks actually.
December 1, 2011 at 7:17 pm
hmmm, it might look a bit like a pile on - can we revert back to the topic? 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply