help with Microsoft SQL select statement between two dates

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

LVL 2
Torrey BatesPreidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
what format are your dates in, something like yyyy-mm-dd ?
0
rockiroadsCommented:
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
Torrey BatesPreidentAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brandonvmooreCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
rockiroadsCommented:
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
Torrey BatesPreidentAuthor Commented:
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
rockiroadsCommented:
why dont u wrap the convert on that med_assist.create_date also
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
<< 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BALMUKUND KESHAVCommented:
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
Torrey BatesPreidentAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.