Solved

Sql Statement

Posted on 2014-01-23
4
360 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 143

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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