SyBase Query Syntax Case When

Hello Experts,

The value of column scheduledate = 12/30/1899 that is a DateTime field

I have this case statement

case when header.scheduledate = 18991230 then 'No' else header.scheduledate end as 'Future Order',

It is not producing my desired results which in this case is 'No'. In this case I am getting '11/1/2015' I have no idea where this date is even coming from?

Thoughts?
triphenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Try with
  case when header.scheduledate = '18991230' then 'No' else header.scheduledate end as 'Future Order',

You are comparing a datetime against a number, which (usually) is interpreted as "number of days since X" (X being something around 1899-12-30). Also keep in mind that scheduledate needs to be exact the same, including the time portion (which is 0:00 = 12 am here).
0
triphenAuthor Commented:
You are comparing a datetime against a number

Where am I doing that?

case when header.scheduledate = 18991230    ???

This is now I reference date fields in SQL and it works fine. I believe the problem here is I am comparing date to datetime.
0
ThomasMcA2Commented:
Yes, your case when is comparing header.scheduledate to a number (18991230 ), not to a date.

To compare it to a date, use the date() function:

case when header.scheduledate = date('1899-12-30')

Open in new window


This may also work:

case when header.scheduledate = date(18991230)

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

PortletPaulfreelancerCommented:
Your original case expression has 2 data types as output
('No' & header.scheduledate)
(a string, a datetime)

plus there is the issue of comparing a datetime to an integer

I suggest this, if you wish to output 'No' or a date in the column "Future":

      CASE
            WHEN header.scheduledate = '18991230' THEN 'No'
            ELSE CONVERT(varchar(10), header.scheduledate, 120)
      END AS [Future Order] --<< do not use single quotes here

the 120 is a style number see:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/Convert.htm

If you do not want a date in that but a 'No' or 'Yes'

      CASE
            WHEN header.scheduledate = '18991230' THEN 'No'
            ELSE 'Yes'
      END AS [Future Order] --<< do not use single quotes here

By the way DO NOT use single quotes when defining a column alias, use either double quotes or square brackets
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
triphenAuthor Commented:
Sweeet. This worked!

By the way DO NOT use single quotes when defining a column alias, use either double quotes or square brackets

What is your reasoning for this?
0
PortletPaulfreelancerCommented:
It's not "my" reasoning, but the advice of the T-SQL authors, however I feel it helps you differentiate between string constants and identities.

(Transact-SQL) Database Identifiers (mssql)
When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.
(Transact-SQL) Constants (mssql)
Character string constants are enclosed in single quotation marks
Whilst I'm not as conversant with the Sybase documentation as I am of MSSQL, both use the same syntax rules. My personal advice is to use brackets (not double quotes) as I find these more intuitive  for identities.
0
triphenAuthor Commented:
Sure makes total sense and looks nicer too :) Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.