?
Solved

help with Microsoft SQL select statement between two dates

Posted on 2009-04-09
11
Medium Priority
?
605 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 200 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 200 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 800 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

764 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