SQL date query based on current date minus 3 days

I need to be able to query my data and return my results based on exam_end_date.  I receive an error based on conversion.

SELECT
dbo.UCH_1_MSH.id,
dbo.UCH_1_MSH.loc_id,
dbo.UCH_1_MSH.billing_number,
dbo.UCH_1_MSH.field_separator,
dbo.UCH_1_MSH.encoding_characters,
dbo.UCH_1_MSH.sending_application,
dbo.UCH_1_MSH.sending_facility,
dbo.UCH_1_MSH.sending_facility_id,
dbo.UCH_1_MSH.event_date,
dbo.UCH_1_MSH.event_type,
dbo.UCH_1_MSH.event_type_code,
dbo.UCH_1_MSH.sequence_number,
dbo.UCH_1_MSH.import_file,
dbo.UCH_2_EVN.event_type_code,
dbo.UCH_2_EVN.event_date,
dbo.UCH_3_PID.corporate_id_2,
dbo.UCH_3_PID.facility_medical_record_number,
dbo.UCH_3_PID.patient_last_name,
dbo.UCH_3_PID.patient_first_name,
dbo.UCH_3_PID.patient_middle_name,
dbo.UCH_3_PID.patient_date_of_birth,
dbo.UCH_3_PID.sex,
dbo.UCH_3_PID.patient_alias,
dbo.UCH_3_PID.patient_race,
dbo.UCH_3_PID.address_1,
dbo.UCH_3_PID.address_2,
dbo.UCH_3_PID.city,
dbo.UCH_3_PID.[state],
dbo.UCH_3_PID.zip,
dbo.UCH_3_PID.patient_home_phone,
dbo.UCH_3_PID.patient_work_phone,
dbo.UCH_3_PID.patient_religion,
dbo.UCH_3_PID.patient_marital_status,
dbo.UCH_3_PID.patient_ssn,
dbo.UCH_4_PV1.patient_class,
dbo.UCH_4_PV1.patient_location,
dbo.UCH_4_PV1.referring_md_id,
dbo.UCH_4_PV1.referring_md_last,
dbo.UCH_4_PV1.referring_md_first,
dbo.UCH_4_PV1.referring_md_middle,
dbo.UCH_4_PV1.admitting_md_id,
dbo.UCH_4_PV1.admitting_md_last,
dbo.UCH_4_PV1.admitting_md_first,
dbo.UCH_4_PV1.admitting_md_middle,
dbo.UCH_4_PV1.admission_date,
dbo.UCH_4_PV1.discharge_date,
dbo.UCH_5_GT1.guarantor_number,
dbo.UCH_5_GT1.guarantor_last,
dbo.UCH_5_GT1.guarantor_first,
dbo.UCH_5_GT1.guarantor_middle,
dbo.UCH_5_GT1.guarantor_address_1,
dbo.UCH_5_GT1.guarantor_address_2,
dbo.UCH_5_GT1.guarantor_city,
dbo.UCH_5_GT1.guarantor_state,
dbo.UCH_5_GT1.guarantor_zip,
dbo.UCH_5_GT1.guarantor_home_phone,
dbo.UCH_5_GT1.guarantor_work_phone,
dbo.UCH_5_GT1.guarantor_date_of_birth,
dbo.UCH_5_GT1.guarantor_sex,
dbo.UCH_5_GT1.guarantor_relationship,
dbo.UCH_5_GT1.guarantor_ssn,
dbo.UCH_6_IN1.sequence_number,
dbo.UCH_6_IN1.ins_plan_id_code,
dbo.UCH_6_IN1.ins_co_id,
dbo.UCH_6_IN1.ins_co_name,
dbo.UCH_6_IN1.ins_address_1,
dbo.UCH_6_IN1.ins_address_2,
dbo.UCH_6_IN1.ins_city,
dbo.UCH_6_IN1.ins_state,
dbo.UCH_6_IN1.ins_zip,
dbo.UCH_6_IN1.group_number,
dbo.UCH_6_IN1.group_name,
dbo.UCH_6_IN1.ins_group_emp_id,
dbo.UCH_6_IN1.ins_group_emp_name,
dbo.UCH_6_IN1.policy_effective_date,
dbo.UCH_6_IN1.polify_expiration_date,
dbo.UCH_6_IN1.subscriber_last,
dbo.UCH_6_IN1.subscriber_first,
dbo.UCH_6_IN1.subscriber_middle,
dbo.UCH_6_IN1.subscriber_relationship,
dbo.UCH_6_IN1.subcriber_date_of_birth,
dbo.UCH_6_IN1.subscriber_address_1,
dbo.UCH_6_IN1.subscriber_address_2,
dbo.UCH_6_IN1.subscriber_city,
dbo.UCH_6_IN1.subscriber_state,
dbo.UCH_6_IN1.subscriber_zip,
dbo.UCH_6_IN1.subscriber_gender,
dbo.UCH_6_IN1.policy_number,
dbo.UCH_7_ST.ord,
dbo.UCH_7_ST.status,
dbo.UCH_8_ORD.exam_end_datetime
FROM dbo.UCH_1_MSH
INNER JOIN dbo.UCH_2_EVN ON dbo.UCH_1_MSH.billing_number = dbo.UCH_2_EVN.billing_number
INNER JOIN dbo.UCH_3_PID ON dbo.UCH_1_MSH.billing_number = dbo.UCH_3_PID.billing_number
INNER JOIN dbo.UCH_4_PV1 ON dbo.UCH_1_MSH.billing_number = dbo.UCH_4_PV1.billing_number
INNER JOIN dbo.UCH_5_GT1 ON dbo.UCH_1_MSH.billing_number = dbo.UCH_5_GT1.billing_number
INNER JOIN dbo.UCH_6_IN1 ON dbo.UCH_1_MSH.billing_number = dbo.UCH_6_IN1.billing_number
INNER JOIN dbo.UCH_7_ST ON dbo.UCH_1_MSH.billing_number = dbo.UCH_7_ST.enc_nbr
INNER JOIN dbo.UCH_8_ORD ON dbo.UCH_1_MSH.billing_number = dbo.UCH_8_ORD.billing_number
WHERE (UCH_7_ST.status = 'P')
and
(UCH_7_ST.ord = 'Y')
and
(UCH_8_ORD.exam_end_datetime = CAST(CONVERT (varchar(10),DATEADD("d", -3, GETDATE()),112)AS DateTime))
ORDER BY UCH_1_MSH.event_date ASC



ERROR
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
cblank1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

gamarrojgqCommented:
Hi,

Try using 111 instead of 112 like this:

and
(UCH_8_ORD.exam_end_datetime = CAST(CONVERT (varchar(10),DATEADD("d", -3, GETDATE()),111)AS DateTime))
0
gothamiteCommented:
Try the CAST function in the penultimate line will depend on your language setting. Try adding this line before your query:

SET DATEFORMAT yyyymmdd

This should match up to the output of the convert with style 112
0
cblank1234Author Commented:
I still get the Conversion faild.. Is there another command that can be used to do the same thing.

The data type is a varchar(20)

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

gothamiteCommented:
if you comment out the
"AND
(UCH_8_ORD.exam_end_datetime = CAST(CONVERT (varchar(10),DATEADD("d", -3, GETDATE()),112)AS DateTime))"

from your query and then add this as an extra column, in what format are the dates in that column?

CONVERT (varchar(10),DATEADD("d", -3, GETDATE()),112) as date_conversion


you might want to do TOP 10 as well in case this results in too many rows!
0
gamarrojgqCommented:
This is another way for what you want

And (
Year(UCH_8_ORD.exam_end_datetime ) = Year(GetDate()-3)
And Month(UCH_8_ORD.exam_end_datetime ) = Month(GetDate()-3)
And Day(UCH_8_ORD.exam_end_datetime ) = Day(GetDate()-3)
)

This way does'nt matter your date format
0
cblank1234Author Commented:
And (
Year(UCH_8_ORD.exam_end_datetime ) = Year(GetDate()-3)
And Month(UCH_8_ORD.exam_end_datetime ) = Month(GetDate()-3)
And Day(UCH_8_ORD.exam_end_datetime ) = Day(GetDate()-3)
)

I still get Conversion failded.. Could there be something wrong with the data or the format the data is in.

SQL Server 2005 SP3,
Field - exam_end_datetime, varchar(20),null.....

Data in the filed
201003260641
201003260743
201003260809
0
gothamiteCommented:
Ah - maybe UCH_8_ORD.exam_end_datetime is not a DATETIME ? If it is a CHAR/VARCHAR it will try to implicitly convert which cause result in that error
0
cblank1234Author Commented:
What type of data type would it need to be to work?
0
gothamiteCommented:
I think you might have some bad dates in that column. Ideally it should be a datetime not a varchar. Try just

select convert(datetime,exam_end_datetime)
from uch_8_ord

if there are any similar errors you have some dudd dates in there.  
0
cblank1234Author Commented:
This is what i'm using now and I'm not getting the error but I'm also not getting any results. I modified the field and created a new field called DOS It is a datetime field and the this statment now works.  ..

select convert(datetime,dos)
from uch_8_ord

(UCH_8_ORD.DOS = CAST(CONVERT (varchar(20),DATEADD(day, -3, GETDATE()),101)AS DateTIME))
0
gamarrojgqCommented:
does your new field DOS have only date? or also have time? because you are comparing the full value of DOS with you CAST that returns only a Date, so If your values in field DOS are like 2010-04-12 14:34:34, you won't have any results, if this is your case, you should CAST your DOS field too like this:

(CAST(CONVERT (varchar(20), UCH_8_ORD.DOS,101) AS DateTIME) = CAST(CONVERT (varchar(20),DATEADD(day, -3, GETDATE()),101)AS DateTIME))
0
cblank1234Author Commented:
I tried that and I'm still not getting any resluts.

The data that is in the new field looks liks this

DOS
04/08/2010
04/08/2010
04/08/2010



0
_agx_Commented:
> Field - exam_end_datetime, varchar(20),null.....
> Data in the filed ... 201003260641

Since the value contains both date (20100326) AND time (0641) grab the first 8 characters ie YYYYMMDD, then compare it.

AND    CONVERT(datetime, LEFT(UCH_8_ORD.exam_end_datetime, 8), 112) =
         CONVERT(datetime, CONVERT (varchar(10), DATEADD(d, -3, GETDATE()), 112),  112)

> Could there be something wrong with the data or the format the data is in.

   Dates should really be stored in datetime columns, not varchar. Then you won't need to use
   slower conversion functions.
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
MySQL Server

From novice to tech pro — start learning today.