selecting month from a date and using it in a SQL Where?


I have a long list of events sorted on dates.
I want to shorten down the list by only showing the dates from one month.

My selectcommand looks like this, so far: SELECT [date], [event] FROM [calendar] ORDER BY [date]"

Is there a way to use WHERE Month(date)=01 ?

or DateTime.Now.Month?



3/11/2008 3:33:13 PM 4679 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 49
Get it on Google Play
Get it on Apple App Store

 You can use the DatePart function:

WHERE DatePart(m, [date]) = 1

I hope that helps. 

3/11/2008 3:58:50 PM

It'll take a combination of the SUBSTRING and CONVERT functions:

SELECT [date], [event], FROM [calendar] WHERE SUBSTRING(CONVERT(varchar(24), [date], 101), 0, 3)=@monthParameter ORDER BY [date]


i know that "101" looks like a magic constant.  Refer to this to see what it means in the convert function:



 Ooooh, wow.  Nice doyle, I like that better.


I never lose, some people are just better than me at winning.
3/11/2008 4:05:18 PM


Is there a way to use WHERE Month(date)=01 ?


WHERE MONTH([date])=1 should work fine.  Alternatively, use the DatePart function as mentioned by someone else, with a small correction:

WHERE DATEPART('m', [date]) = 1

You need quotes around the part of the date in Access, as the function takes a string.  SQL Server doesn't need them because it uses symbols.



Regards Mike
My site
3/12/2008 8:18:09 AM


It'll take a combination of the SUBSTRING and CONVERT functions:


Neither SUBSTRING or CONVERT are available in Access.


Regards Mike
My site
3/12/2008 8:19:28 AM

Similar Artilces:

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Incorrect selection of the actual date using Embedded SQL in MS SQL / OLE-DB
we're migrating our application from PB7 to PB10 ( using MS SQL Server ), and i'm having a problem when selecting the date and time from SQLServer; In embedded SQL, the result-set doesn't show the time of actual date... Example: At 15/02/2005 16:15:20 executing the instrucion below, datetime ldt_now select getDate() into :ldt_now from tb_x; It gets 15/02/2005 00:00:00 in ldt_now... PS: The object transaction is configured as soon: // Profile x SQLCA.DBMS = "OLE DB" SQLCA.LogPass = "gsdf" SQLCA.LogId = "gfsd" SQLC...

Strange Date format behavior when selecting by date, using MS SQL server 2005 express edition
In my local desk machine, regional settings for date have the following format    dd/mm/yyyy (its the standard im my country) In my application using MS SQL server 2005 express edition, when I try do recover records using a date as parameter, the only way to recover the right records  is modifying the date format so that de original values in the format  of dd/mm/yyyy is changed to mm/dd/yyyy For example the right date is   13/09/2008 (dd/mm/yyyy)  (all the records where added the table using this date in this forma...

How to use dates in an SQL select statement
Hi, I have two combo/dropdown boxes. One lets user pick a month (January-December), one lets them pick a year (2003 or 2004) I want to take the values of these to form an SQL statement. In my database is a field called date which holds dates in the format 23/05/2003 i.e. day/month/year Currently my code is: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim month, year As String ' Find out month we want to search Select Case ComboBox1.SelectedItem Case "January...

How to achieve this using SQL with Access mdb as datasource ?
I have an Access table like this:- Name, Value, Effective ============ John,12, 1 Jan 2008 John, 14, 1 Jun 2008 John, 16, 1 Jan 2009I want add an end date to make it like:- Name, Value, Effective, Enddate ============ John,12, 1 Jan 2008, 31 May 2008 John, 14, 1 Jun 2008, 31 Dec 2008 ie what I need is to add enddate as additional field which is the Start date of next record minus 1. Is this possible ? Thanks in advance for the help. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped. Pls help........... Thanks....

Using a Date Function to select a column in Access
Hi all. Was wondering if you could help me. Firstly what I have is seven different tables,each one donating different room types. Each entry is deignated by a number that ties it into the other tables. Each of my columns is a date and the cell underneath is the price of the room on the day. What I am trying to do is create a SQL query either from a ASP page or in access itsself where I can pull the colums and all the room prices for the corresponding dates for 14 days from todays dates. So no matter what date it is the first entry will be todays date. This is what I have so far SELE...

Calculating Months Between Two Dates using Access
I am developing a web-based Access dbase and I need to calculate the number of months between the current date (NOW()) and the date of employment input by the user. The calculated value will be stored in the Access dbase table and displayed in a datagrid and as a read-only label in a form. The two fields in the dbase table are "empDate" and "time_in_Service". I want the calculation to originate and reside in the "time_in_Service" field in the physical dbase, instead of using script on the page to calculate it. I have tried using the DateDiff function in Acc...

selecting data using month portion of date
i am trying to return a data set by selecting a specific month from a dropdown list and using the selected value as the parameter. unfortunately i have not been able to get this to work. i have tried a SQLdatasource and an Object data source but still having problems. if anyone can help point me in the right direction for the best option to use i would appreciate it. Try getting the dataset first, and then, on selection change of the dropdown, filter the dataset based on the month selected.Rgds,Neolsoft~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Mark as Answer if it helps you~~~~~~~~~~~~~~~~~...

How to access SQL server when using visual studio.NEt
I use web matrix to create .NEt data base application. I am able to create tables and database on sql server using web matrix. However is there any way to do the same on visual studio .net Shaun You should be able to do this to SQL Server instances that you register with the Server Explorer tab in VS.Net...

how do i use todays date in an sql select query with sqldatasource.
...for instance i want to select * from lists where publishdate > todayDate() do i need to include todays date as a selectparameter? can you provide example please? Sql Server:  select * from lists where publishdate > GetDate()Access: select * from lists where publishdate > Date()Regards Mike [MVP - ASP/ASP.NET]My site Thanks. by the way is there a way i can get just this part of date 12/12/2007 as opposed to 12/12/2007 17:42:00 thanks for help hi,no need for a select parameter, just use the system function getdate()  to get the current datehth bonski's b...

retrieve selected data from SQL database using
I already watch the video in the express to learn about the data binding. But the problem that i face is when i want to retrieve certain data from the database. the video just show how to fill and update. who ever know the code to retrieve the selected row from the SQL database using 2005????? thanks do u want to retrive a particular cell or a particular row from the database?what control do you want to bind the retrived value to? thanks Imran NathaniLet the game begin!!Barcode image generation made easy.Writing an ActiveX Control in C#Generating Relational Data From XML D...

cannot access sql databases through iis using .net 1.1
Created a project using web matrix, when I run the application using web matrix server it works fine and my sql database get accessed and web pages are update with info from the sql database. Then I try running application using iis and the first web page displays correctly but when it tries to access the database to refresh the page with data from sql database it fails. Iget Login failed for user 'AMD2200\ASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and wh...

Date Times into MS-SQL server database using .Net
I keep getting errors when trying to input a date time string into an MS SQL Server database and i'm begining to loose patience with it. My field is set up as "datetime" field with a length of 8 (bits ?) My sql string to input into that database is tehn concatonated like so (using sql and mystring = "UPDATE tablename SET mydatefield='" & DateTime.Now & "' WHERE id=" & frmid I keep getting the following error.... The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statem...

.NET cannot run a select using a DataAdapter or DataReader for SQL Anywhere 7
I am writing a .NET program in C# to interface with a SQL Anywhere 7 database. I have no problems connecting to the database or even doing inserts, updates and deletes using ExecuteNonQuery, however, anytime I use a DataReader or DataAdapter against a SELECT statement I get the following error: 'System.InvalidCastException' occurred in' The same code works if I change it to access SQL Server. Here is the code.... Thanks for any help on this issue... OleDbConnection conn = new OleDbConnection("provider=ASAProv;data source=xxxxx;uid=xxx;pwd=xx...

Web resources about - selecting month from a date and using it in a SQL Where? -

Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 ...
The " Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 " was a document published on ...

Voting Machine Caught on Camera Casting Ballot for Democrat when Selecting Republican - YouTube
**UPDATE.... I've heard many reports that this is "just a calibration issue." I will say this... when I went to page 2 and began voting for city ...

Selecting The Right Jeans
Fit, style, cut, colour and wash all factor into the pursuit for great denim.

Clock ticking on selecting successor to Leigh Clifford at Qantas
With Qantas on course to chalk up its largest annual profit since 2008, the question now is whether chairman Leigh Clifford is looking to hang ...

Why are the reserves selecting the team?
Backbenchers are the reserves of the parliamentary system. They are newcomers.

Jackie French: Why selecting the right place for your plants is important
The secret of growing anything easily is to choose the best spot for what you want to grow.

Netflix turns to pirates when selecting new shows
Imagine the surprise, then, when the popular video streaming site admitted that it actually turns to torrenters for inspiration. "With the purchase ...

Boston Marathon bombing trial: Judge, lawyers face challenges in selecting jurors - CTV News
As the second phase of jury selection began in the federal death penalty trial of Boston Marathon bombing suspect Dzhokhar Tsarnaev, it immediately ...

Selecting cabinet one of first orders of business for Trudeau
One of the first orders of business for any new prime minister is picking a cabinet. Who is likely to make the cut?

Agile Analysis, Self-Selecting Teams, TDD & BDD in Methods & Tools Winter 2014 issue
... for software developers, testers and project managers – has just published its Winter 2014 issue that discusses Agile Analysis, Self-Selecting ...

Resources last updated: 12/31/2015 7:04:28 PM