Solved

Sql Statement

Posted on 2014-01-23
4
355 Views
Last Modified: 2014-01-23
I need some help with creating a sql statement that can be used with html page.  I need to pull data from a field within table and show the results as either yes or no
I have two types - one is a date field and the other is either 1 or 0.  The one giving me a problem is the date - the default for the field is 1/1/1900 so I need to show any with this date as no and any with a date other than this as YES.  I hope has enough information for someone to help me if not please let me know.  I have tried this basic statement and customizing for my data and using a date but keep getting errors.

Select Case When IsNull(FieldName, 0) = 1 Then 'Yes' Else 'No' End As FieldName From TableName
0
Comment
Question by:jajen
4 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 39803018
if the field is date data type, then the issue is with the 0 as default value...

this is the way I would start coding:
Select Case When FieldName IS NULL then 'No'
   when FieldName <= convert(datetime, '1900-01-01', 120) then 'No' else 'True' End As FieldName 
From TableName  

Open in new window

0
 
LVL 12

Assisted Solution

by:jmcmunn
jmcmunn earned 150 total points
ID: 39803059
Assuming the field will never be null (since you said you have a default value) it would just be this:

SELECT CASE WHEN [theDateField]= '1900-01-01' THEN 'No' ELSE 'Yes' END AS YesOrNo
FROM [TableName]

Otherwise if it is null, add in another when/where statement for the null value.
0
 
LVL 18

Accepted Solution

by:
x-men earned 200 total points
ID: 39803062
Select Case
  When datefield = '1/1/1900' Then 'no'
Else 'yes'
End As FieldName From TableName
0
 

Author Closing Comment

by:jajen
ID: 39803091
Thank you all for answers - sometimes simple is the way to go - I was making it more difficult than needed.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now