[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

help with Microsoft SQL select statement between two dates

Posted on 2009-04-09
11
Medium Priority
?
607 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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