Solved

Sql Statement

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

770 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