[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

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

0
Torrey Bates
Asked:
Torrey Bates
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 & ArchitectCommented:
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 & ArchitectCommented:
<< 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
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now