Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1608
  • Last Modified:

Convert str to date and then compare

I need to compare 2 date fields; one is a string, the other is a datetime.  The string is in the yyyymmdd format.  How do I convert these two data types, and then compare them?  I will want to display data where the data associated with the string is more recent than the datetime.
0
AMS_RN
Asked:
AMS_RN
  • 2
1 Solution
 
MaduKpCommented:
Could you please give more details?
what is the database?
what is the programming language you are using?
0
 
AMS_RNAuthor Commented:
Sorry about that;  I'm using Crystal Reports XI, and the database is in sql (it's a healthcare database.)  I am comparing the date that a medication was stopped (which is a string) to the date of the office visit (which is a datetime).  I only want to display medications that have a stop date AFTER the office visit date (so the medication is still current when the patient leaves the clinic).
0
 
MaduKpCommented:
Ok ,
Please see below SOL statement, you can use this as a SP and use,
Use <Your DB - healthcare database>

========================================================================

DECLARE  @medication_stopped datetime
SET @medication_stopped = '2008-06-11'  send your date here

To compare two dates is done as below ,Here the table_2 is <whatever your table have the filed date of the office visit> 

select * from table_2
where convert(varchar(8), date_of_the_office_visit)
< convert(varchar(8), @medication_stopped)

=========================================================================

Suppose your table is something like this,
ID            date_of_the_office_visit
1      06/10/2008 12:00:00 AM
2      06/11/2008 12:00:00 AM
3      06/07/2008 12:00:00 AM
4      06/19/2008 12:00:00 AM
5      09/10/2008 12:00:00 AM
6      02/10/2008 12:00:00 AM

The output will be
1      2008-06-10 00:00:00.000
3      2008-06-07 00:00:00.000
6      2008-02-10 00:00:00.000
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now