Solved

Convert str to date and then compare

Posted on 2008-06-09
3
1,599 Views
Last Modified: 2012-06-22
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
Comment
Question by:AMS_RN
  • 2
3 Comments
 
LVL 10

Expert Comment

by:MaduKp
ID: 21749126
Could you please give more details?
what is the database?
what is the programming language you are using?
0
 

Author Comment

by:AMS_RN
ID: 21750977
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
 
LVL 10

Accepted Solution

by:
MaduKp earned 125 total points
ID: 21757590
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Survey software 5 50
Is Windows 10 now more stable 43 148
Resize disk partitions on a server 32 110
Software (free or not) to catalog a collection of software and freeware 3 75
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now