Date Range Question (1 Viewer)
- Thread starter JamesJoey
- Start date
- Local time
- Today, 00:10
- Joined
- Feb 19, 2002
- Messages
- 43,223
Date format is irrelevant. The question is WHAT MONTH? is it always going to be the current month or do you want to specify a month?
I have dated records that start on Nov. 1 and run all month I need to see which record start on Nov. 1 and run to Nov. 14.
Then, I want to see the records that go from Nov. 15 to the last day of the month.
Subsequently, I'll do the same thing for Dec.
Then, I want to see the records that go from Nov. 15 to the last day of the month.
Subsequently, I'll do the same thing for Dec.
isladogs
MVP / VIP
- Local time
- Today, 05:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
Pat,
I disagree. It depends on how the records are obtained.
There are various ways of getting the records for the first 14 days of each month. My first thought for doing so was to either filter the first section (dd/mm/yyyy format) or the middle section (mm/dd/yyyy) format.
If you choose to use DatePart then that's a different matter.
I assumed that James would want this done for each month
- Local time
- Today, 00:10
- Joined
- Feb 19, 2002
- Messages
- 43,223
If you don't format your dates into strings, you won't ever have trouble with them. Formatting is only for human consumption. Dates internally are stored as double precison numbers and if you work entirely with date objects, you won't have a problem. Only if you have to create an SQL string with an embedded date string will you have to worry about the date format and in that case, it must either be standard US format - mm/dd/yyyy or the more universal and less ambiguous - yyyy/mm/dd
Guess what - if you use a formatted string 01/01/2018 is less than 11/11/2017!!! Why? because strings are compared character by character, left to right and 0 is less than 1.
JamesJoey tried but he didn't answer my question. The question is - TODAY do you only want to select records between Nov 15 and Nov 30? OR might you want to look at data from OTHER periods? If TODAY'S date ALWAYS controls what you see, then the query would always use the Date() function to control the selection. If you might want to see other periods, then you need to use a form and have the user pick Year, Month, and period 1 or period 2.
Please try again to answer the question.
Guess what - if you use a formatted string 01/01/2018 is less than 11/11/2017!!! Why? because strings are compared character by character, left to right and 0 is less than 1.
JamesJoey tried but he didn't answer my question. The question is - TODAY do you only want to select records between Nov 15 and Nov 30? OR might you want to look at data from OTHER periods? If TODAY'S date ALWAYS controls what you see, then the query would always use the Date() function to control the selection. If you might want to see other periods, then you need to use a form and have the user pick Year, Month, and period 1 or period 2.
Please try again to answer the question.
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 05:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
All true - but if you use a query then dd/mm/yyyy can be used if that's the default format as in the UK
For example this query SQL:
Code:
SELECT tblPostcodeAddressDates.Postcode, tblPostcodeAddressDates.AddressDownloadDate
FROM tblPostcodeAddressDates
WHERE ((Left([AddressDownloadDate],2)<15 And Mid([AddressDownloadDate],4,2)=11));returns these records from a table I'm currently working on:
I'm not saying this approach is the best way of doing what James wants.
However, it is one perfectly valid method
I'm fully aware of that. That's why postcodes starting with AL2 would be sorted after those beginning AL10
He didn't answer that question but that wasn't what you asked him!
Perhaps I'm wrong but I can't imagine any reason why you would only want to select data for the first half of the current month.
I'll leave that for James
Attachments
-
Capture.PNG
Last edited:
- Local time
- Today, 00:10
- Joined
- Feb 19, 2002
- Messages
- 43,223
Using string functions against a date field is your problem. Use date functions. Then you have
WHERE Day([AddressDownloadDate])<15 And Month([AddressDownloadDate])=11; -- see, format is irrelevant
ALWAYS use Date functions to work with dates and format is irrelevant because dates are not stored as strings dates are stored as double precision numbers with the date being the number of days since Dec 30, 1899 (Jet/ACE, the origin for SQL Server and Excel is different) and time being the fraction of a day since midnight as the decimal.
print now()
11/17/2017 11:19:33 PM
print cdbl(#11/17/2017 11:19:33 PM #)
43056.9719097222
WHERE Day([AddressDownloadDate])<15 And Month([AddressDownloadDate])=11; -- see, format is irrelevant
ALWAYS use Date functions to work with dates and format is irrelevant because dates are not stored as strings dates are stored as double precision numbers with the date being the number of days since Dec 30, 1899 (Jet/ACE, the origin for SQL Server and Excel is different) and time being the fraction of a day since midnight as the decimal.
print now()
11/17/2017 11:19:33 PM
print cdbl(#11/17/2017 11:19:33 PM #)
43056.9719097222
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 12:10
- Joined
- May 7, 2009
- Messages
- 19,231
to get the records for the Current Month, 1-14 use Criteria:
[yourDateField] Between DateSerial(Year(Date), Month(Date), 1) And DateSerial(Year(Date), Month(Date), 14)
to get records of Current Month 15-lastday of Current Month:
[yourDateField] Between DateSerial(Year(Date), Month(Date), 15) And DateSerial(Year(Date), Month(Date) + 1, 0)
[yourDateField] Between DateSerial(Year(Date), Month(Date), 1) And DateSerial(Year(Date), Month(Date), 14)
to get records of Current Month 15-lastday of Current Month:
[yourDateField] Between DateSerial(Year(Date), Month(Date), 15) And DateSerial(Year(Date), Month(Date) + 1, 0)
isladogs
MVP / VIP
- Local time
- Today, 05:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
Pat,
I don't have a problem other than being talked down to...
I gave a perfectly valid solution which I qualified with:
Arnelgp has just given another more general solution which is equally valid.
James now has a variety of responses to choose from/ adapt.
Personally I have no 'problem' whichever he chooses.
Yes, I'm fully aware of that fact and regularly work with dates in their native double format.
I'm also about to send you a PM
- Local time
- Today, 00:10
- Joined
- Feb 19, 2002
- Messages
- 43,223
Sorry if I offended you ridders. I shouldn't try to be funny. However, it is important that you use the correct functions if you want to avoid problems. Using string functions to manipulate dates causes problems. Formatting dates using the Format() function converts them to strings at which point they act like strings rather than dates. I have responded to hundreds of questions over the years regarding date issues and they all came down to using string functions when the poster should have been using date functions. It becomes even more complicated when your local date format is not the US standard of mdy order because in that case, there are situations where you need to use Format() to produce a date string in US standard order for use in an SQL String.
I never offered specific code for JamesJoey because he never responded to my question regarding how he wanted to pick a date range. The solution offered by arnelgp is what I would have suggested if the answer was - I always want to view the date range based on today's date. However, I seriously doubt that is the real answer but we'll never know unless JamesJoey responds.
I never offered specific code for JamesJoey because he never responded to my question regarding how he wanted to pick a date range. The solution offered by arnelgp is what I would have suggested if the answer was - I always want to view the date range based on today's date. However, I seriously doubt that is the real answer but we'll never know unless JamesJoey responds.
Similar threads
