Solved

help with Microsoft SQL select statement between two dates

Posted on 2009-04-09
11
604 Views
Last Modified: 2012-05-06
This has probably been asked a hundred times but I could not find anything on the Exchange to fix my problem.

I built a query string for my SQL select statement that I simply want to show all records between two dates. (IE: 04/01/2009 - 04/10/2009)

The SQL table column "create_date" is a DATETIME type and I don't want the time to be a factor.  I believe I need to change this statement to truncate the time off to make the between statement work.

"m_create_date1" and "m_create_date2" are vbscript variables I assign and fill from FORM field textfield that are 10 chars.  So I need the time knocked off the SQL column field and some kind on function on these two vbscript variables to convert them to a DATE format that can work with the query.

Regards,
Torrey
<%
query_string = "SELECT * FROM med_assist WHERE (med_assist.create_date BETWEEN " & m_create_date1 & " AND " & m_create_date2 & ")"
%>

Open in new window

0
Comment
Question by:Torrey Bates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24113529
what format are your dates in, something like yyyy-mm-dd ?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 50 total points
ID: 24113535
or maybe use the convert to change it to a date
eg http://doc.ddart.net/mssql/sql70/ca-co_1.htm
convert(char(8),mydatefield,112)

0
 
LVL 2

Author Comment

by:Torrey Bates
ID: 24113537
In the SQL table when I view the table rows they are:  
mm/dd/yyyy hh:mm:ss AM   - don't care about the time and AM/PM

When entered in the form fields I check and force that they are entered as mm/dd/yyyy

Regards,
Torrey
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24113540
You can use DAY(), MONTH(), YEAR() functions to get just those parts of the date, or you can use DATEPART() to get a formatted date result.  If you need more info on these you can go to: http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24113542
Try out this query:

SELECT *
FROM med_assist
WHERE med_assist.create_date BETWEEN CONVERT(datetime, m_create_date1, 101) AND CONVERT(datetime, m_create_date2, 101)

Assuming your input date format will be in mm/dd/yyyy, I have used 101 format in my above query. If you use some other date format then replace 101 with the one that is valid from the link below

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24113587
the link I gave you gives you the different styles. I put in 112 which is yymmdd, if you put in 101, that gives you mm/dd/yy so change that. It is still length 8, so no need to change that
0
 
LVL 2

Author Comment

by:Torrey Bates
ID: 24113594
brandonvmoore, I tried DATEPART() before posting but just couldn't figure it out.  Can you modify my select statement to show a working solution?  If the other suggestions with using CONCERT are less code I prefer that.

rockiroads and rrjegan17 - it sounds like you are on the right track.

rrjegan17 - I tried your sample code and it doesn't crash but I get no results back.  I think the problem is the with the SQL table column I comparing to right after the WHERE clause.  The "med_assist.create_date " is a DATETIME type - I need a function to knock the time off so when it is compared to the CONVERTS they line up.

Regards,
Torrey
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 50 total points
ID: 24113604
why dont u wrap the convert on that med_assist.create_date also
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 200 total points
ID: 24113616
<< I need a function to knock the time off so when it is compared to the CONVERTS they line up. >>

If you dont want time values to be compared, then your query should be somewhat like this

SELECT *
FROM med_assist
WHERE CONVERT(DATETIME,CONVERT(CHAR(10),med_assist.create_date,101), 101) BETWEEN CONVERT(datetime, m_create_date1, 101) AND CONVERT(datetime, m_create_date2, 101)

This query will not compare any time based comparison. Is this what you require.
Make sure you have records in the table that satisfies this date range to obtain results
0
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24113687
WHY YOU DON'T SELECT YOUR DATE AS STRING AND TRUNCATE YOUR TIME PORTION IN YOUR SELECT STATEMENT ?
SELECT * FROM XYZ WHERE MID(STR(DATE1),1,10)>="01/01/2009" AND MID(STR(DATE1),1,10)<="
"01/31/2009"
0
 
LVL 2

Author Closing Comment

by:Torrey Bates
ID: 31568822
I split the points becuase rockiroads pointed out first using the CONVERT method and later mentioned wrapping the CONVERT around the SQL table column name.

However, rrjegan17 gave me a complete full working example and also noticed I needed the year as 4-digits with CHAR(10) and also resolved excluding the TIME from the mix.

Thanks you both so much and I  hope everyone is happy!

Regards,
Torrey
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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