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.
Author |
Topic |
rbara902
Starting Member
2 Posts |
Posted - 2014-04-16 : 12:46:39
|
Hello,I'm fairly new to SQL. I have a function in Excel that i'm trying to translate to SQL to run my query. Basically what i want to do is if Date1 and Date2 are blank then display Date Not Tracked. Else if Date1 is blank however Date2 has a value then pull Date2 other wise Date1This is my Excel function:=IF(AND(A2="",B2=""),"Date Not Tracked",IF(A2="",B2,A2))A2 = Product.Date1B2 = Product.Date2Can anyone help me?Becky |
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-04-16 : 13:02:32
|
Hi rbara902, is this something what you are looking for.SELECT CASE WHEN Product.Date1 = '' THEN CASE WHEN Product.Date2 = '' THEN 'Date Not Tracked' ELSE Product.Date2 END ELSE Product.Date1 ENDFROM Table_name Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-04-16 : 14:58:34
|
Becky,what are the data types of Date1 and Date2? Assuming they are some kind of date like DATETIME then your "blanks" must be NULLs rather than empty string ('').And in that case the suggested code won't work because 'Date Not Tracked' is varchar and you can't conditionally return different data types in a CASE expression.Be One with the OptimizerTG |
|
|
rbara902
Starting Member
2 Posts |
Posted - 2014-04-16 : 15:11:39
|
TG you are correct Date1 and Date2 are date fields. Any suggestions on how i could accomplish this?Sqlsage - thank you for the suggestion! i will try it out :)Becky |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-04-16 : 15:13:54
|
Here's one way:This means that the return type is varchar and can no longer treat the output as dates but here ya go. And you can always chose a different date format code.select coalesce(convert(varchar(23), coalesce(date1, date2), 121), 'Date Not Tracked')EDIT:for more info on format options and converting data types in general see Cast and ConvertBe One with the OptimizerTG |
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-04-16 : 15:28:11
|
quote: Originally posted by TG Becky,what are the data types of Date1 and Date2? Assuming they are some kind of date like DATETIME then your "blanks" must be NULLs rather than empty string ('').And in that case the suggested code won't work because 'Date Not Tracked' is varchar and you can't conditionally return different data types in a CASE expression.Be One with the OptimizerTG
Yes, I thought the same too. But when the TS specifically mentioned about Blankspaces I refrained myself from using COALESCE.. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles. |
|
|
|
|
|
|
|