473,289 Members | 2,089 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

to query all days of a month

1
Hi, is there a way to query all days of a given month?
for ex: there should be 31 records for January..28 for february like that..
Aug 23 '07 #1
2 2286
dhutton
28
Try this - When I want to Query all the days in a month - for example July1 thru July31 would be:

SELECT MyData

FROM MyTable

WHERE MyDate BETWEEN '20070701' AND '20070731'

The date format has to be the same as whats in your table - provided that you DO have a Date field.
Aug 23 '07 #2
ck9663
2,878 Expert 2GB
Try this - When I want to Query all the days in a month - for example July1 thru July31 would be:

SELECT MyData

FROM MyTable

WHERE MyDate BETWEEN '20070701' AND '20070731'

The date format has to be the same as whats in your table - provided that you DO have a Date field.

just in case you don't have a datefield, put this in a function. it's currently defaulted to the current year. you can pass the year to the function, the code is already handling leap year...


Expand|Select|Wrap|Line Numbers
  1.  
  2. select top 12 
  3. datename(month,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))),
  4. datename(year,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))),
  5. datepart(dd,dateadd(ms,-3,dateadd(mm, datediff(m,0,cast(id as varchar(2))+'/01/'+cast(datepart(year,getdate()) as varchar(4)))+1, 0))) as numofday
  6. from sysobjects
  7. where id <= 12
  8. order by id asc
  9.  
Aug 24 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: cover | last post by:
I have a form with drop down menus to query for name, month, and year to capture activity accordingly by an individual for a given month and given year. But I'd like to also be able to query ALL...
4
by: Chris | last post by:
Having a little trouble returning the result I'm looking for in a query and thought I'd ask a question here. I have an input screen with a pull down menu to capture vacation activity within a...
7
by: Fred Thompson | last post by:
I'm using MS SQL 2000 server. I have a table which includes a date field which has people's birthdays in it. How can I write a query to return all the records of people with birthdays within the...
2
by: Dan | last post by:
Hi all! I am creating a Disney "Today in History" database, and so far have about 300 entries in it. Well, my Access database is great, and my Frontpage form is great, but I want to include a...
3
by: colmobrien | last post by:
Help Needed ms access 2000 I have a table where one field is the year end monthr (January, February,........, December) and another field is the year end day (1,2,3,........,31) i want to...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
7
by: burnside | last post by:
Hi - long time viewer, first time poster i have a car booking system that stores the startdate and the enddate of a booking as well as car/customer details. i want to produce an efficiency report...
4
by: tbramley | last post by:
Hi, I've got a cross tab query which is sorted by area then month but the month is sorted in alphabetical order and I was wondering if this can be done in Month order e.g. April, May, June, July...
1
osward
by: osward | last post by:
Hi everyone, Background 1. I have a table that consits 400+ rows of data and is growing by day. The table already has paging links at the bottom but I restricted to display rows of data only >=...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.