• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

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.
0
cblank1234
Asked:
cblank1234
  • 5
  • 4
  • 3
  • +1
1 Solution
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now