SQl Code Null values

Hi Experts (note this is a follow on question from)

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28238556.html

I have know been to the SQL guys and they have said the following

use Apples_Oranges
Select fos_datetime_new_op
FROM tbl
where isdate(fos_datetime_new_op)=0
and fos_datetime_new_op is not null

gives 0 rows.

So I would say that the issue is the null values. If your script filters out the null values, it should then work.

The date in the field is in the format: ‘2011-11-21 15:30:30.383’

I am still tying to resolve the issue

CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],

MY QUESTION IS HAVE THE SQL GUYS HERE ON MY SIDE TOLD ME ANYTHING NEW....the EE genuis have not all ready previous told me,,,,,in getting this resolved?
route217JuniorAsked:
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.

Lee SavidgeCommented:
What version of SQL are you using?
0
Lee SavidgeCommented:
isdate() returns a 1 if the date is a valid datetime and 0 otherwise. In SQL 2012 it returns other values depending on the data type.
0
route217JuniorAuthor Commented:
Hi ISavidge

using sql 2008....should that make a difference???
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lee SavidgeCommented:
It only makes a difference if you're using SQL 2012.

SQL 2008 and SQL 2008 R2 use the original isdate() function.

http://technet.microsoft.com/en-us/library/ms187347%28v=sql.100%29.aspx

http://technet.microsoft.com/en-us/library/ms187347%28v=sql.105%29.aspx
0
Lee SavidgeCommented:
If you're looking to return all valid dates where the the field value is not null then your query should be this

use Apples_Oranges
Select fos_datetime_new_op
FROM tbl
where isdate(fos_datetime_new_op)=1
and fos_datetime_new_op is not null

Open in new window

0
route217JuniorAuthor Commented:
isavidge

how would I amend


CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],fos_dateonly_new_omb,CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],

as previous reply back....

or do the sql guys need to do something on my end...
0
Lee SavidgeCommented:
This is a different question. Your initial question was to ask how to filter out records that aren't a valid date and ignore null values which this does:

where isdate(fos_datetime_new_op)=1
and fos_datetime_new_op is not null

What this is doing

CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],fos_dateonly_new_omb,CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],


is to convert the dates first of all into an 8 digit string in the form yyyymmdd and then cast it to a date which gives you a SQL data value with the time element zero'd

All you need is to apply the where clause but it would help if I saw the whole select statement for real.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
lsavidge,

No, isdate( ... ) = 1 selects valid dates only.
0
route217JuniorAuthor Commented:
isavidge
SQL Select Statement...

SELECT 

snapshot_date, 
contact_id,
demand_type,
valid_case_ind,
date_in_bank_datetime,
CAST([date_in_bank_dateonly] AS DATETIME) AS [date_in_bank_dateonly],
date_in_cr_datetime,
CAST([date_in_cr_dateonly] AS DATETIME) AS [date_in_cr_dateonly],
case_created_datetime,
CAST([case_created_dateonly] AS DATETIME) AS [case_created_dateonly],
case_status,
Workstream,
product_01,
product_02,
product_03,
product_04,
cal_days_to_cr,
bus_days_to_cr,
cal_days_to_log,
bus_days_to_log,
case_created_hour,
CAST([init_dateonly_closed] AS DATETIME) AS [init_dateonly_closed],
init_tr_calls,
init_tr_contacts,
init_tr_calls_ind,
init_cal_days_os_bank,
init_bus_days_os_bank,
init_cal_days_os_cr,
init_bus_days_os_cr,
init_cal_days_os_created,
init_bus_days_os_created,
reopen_datetime,
CAST([reopen_dateonly] AS DATETIME) AS [reopen_dateonly],
reopen_hour,
reopen_und_calls,
reopen_und_contacts,
reopen_und_calls_ind,
reopen_und_contacts_ind,
reopen_cla_calls,
reopen_cla_contacts,
reopen_cla_calls_ind,
reopen_cla_contacts_ind,
reopen_cal_e2e_bank_closed,
reopen_cal_e2e_cr_closed,
reopen_bus_e2e_bank_closed,
reopen_bus_e2e_cr_closed,
reopen_cal_e2e_created_closed,
reopen_bus_e2e_created_closed,
paf_outcome,
paf_final,
paf_date,
CAST([ack_breach_dateonly] AS DATETIME) AS [ack_breach_dateonly],
ack_breach,
ack_breach_backdated,
CAST([four_week_breach_dateonly] AS DATETIME) AS [four_week_breach_dateonly],
four_week_breach,
four_week_breach_backdated,
CAST([eight_week_breach_dateonly] AS DATETIME) AS [eight_week_breach_dateonly],
eight_week_breach,
eight_week_breach_backdated,
CAST([ack_potential_dateonly] AS DATETIME) AS [ack_potential_dateonly],
CAST([four_week_potential_dateonly] AS DATETIME) AS [four_week_potential_dateonly],
CAST([eight_week_potential_dateonly] AS DATETIME) AS [eight_week_potential_dateonly],
qc_first_decision,
qc_first_decision_rating,
qc_first_decision_area,
qa_first_decision_rating,
qa_first_decision_area,
qa_first_decision_overturned,
qc_second_decision_overturned,
qa_second_decision,
qa_second_decision_overturned,
CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],
fos_dateonly_closed_omb,
fos_cal_days_to_close_op,
fos_cal_days_to_close_omb,
fos_bus_days_to_close_op,
fos_bus_days_to_close_omb,
fos_os_cal_day_op,
fos_os_cal_day_omb,
fos_os_bus_day_op,
Last_Updated,
original_group,
current_group,
Contact_Reference,
Status_Updated,
week_breach_4,
Breach_8_Date,
CAST([Breach_Ack_Dateonly] AS DATETIME) AS [Breach_Ack_Dateonly],
CAST([Breach_Area_Dateonly] AS DATETIME) AS [Breach_Area_Dateonly],
breached_1,
closed_non_reportable,
CR_Action_Taken,
cr_summary,
cust_req_amount,
date_handedin,
date_fos_closed,
executive_name,
Final_Desc_01,
Final_Desc_02,
FOS_Invoice,
Handed_In_From,
High_Profile,
Hot_Topic

FROM  tbl

Open in new window

0
Lee SavidgeCommented:
@Qlemo


No, isdate( ... ) = 1 selects valid dates only.

I know it selects valid dates. That's the point. The select statement is supposed to select valid dates and where the field is not nulll only.
0
PortletPaulfreelancerCommented:
REMOVE the "(8)" in your conversion (as noted in an earlier question)

and:

wrap the conversion inside a case expression.

use Apples_Oranges
Select 
fos_datetime_new_op
, case when isdate(fos_datetime_new_op) = 1 then CAST(CONVERT(varchar, [fos_datetime_new_op], 112) AS datetime) 
       else null
  end AS fos_datetime
FROM tbl
;

Open in new window

AND consider these alternatives:
-- option 1 with varchar
declare @fos as varchar(40) = '2011-11-21 15:30:30.383'

select
      @fos as fos
    , case when isdate(@fos) = 1 then CAST(CONVERT(varchar, @fos, 112) AS datetime) 
           else null
      end AS fos_datetime
;

-- option 2 direct to datetime
declare @fos as varchar(40) = '2011-11-21 15:30:30.383'

select
      @fos as fos
    , case when isdate(@fos) = 1 then CONVERT(datetime, @fos)
           else null
      end AS fos_datetime
;

-- option 3 direct to date (no time)
declare @fos as varchar(40) = '2011-11-21 15:30:30.383'

select
      @fos as fos
    , case when isdate(@fos) = 1 then CONVERT(date, @fos)
           else null
      end AS fos_datetime
;

Open in new window

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.