This is a feature that needs to be implemented today when developing systems
According to the selected date, the query related content, but not according to the whole date to filter, but based on, year, month, Day split form to filter, such as 2013, February
In SQL Server, we can do it directly according to DatePart.
SELECT * from pays where 1=1 and datepart (year, pdate) =2013 and datepart (month, pdate) =2 and DatePart (day, pdate) =26 order by pdate
But it doesn't work in access because the methods are different and need to be written
SELECT * from pays where 1=1 and datepart ("yyyy", pdate) =2013 and datepart ("M", pdate) =2 and datepart (' d ', pdate) =21 order by pdate
Functions about SQL Server in the first place, now summarize the date functions of access
How to convert text type: 2003.08.04 to date type: 2003-08-04
CDate (replace ("2003.08.04", ".", "-"))
Displays the week number of the current date in the year
=format (now (), "WW")
WW is 1 to 53.
Displays the four-bit year value of the Date field value.
=datepart ("yyyy", [Order Date])
Displays the date value 10 days before the Date field value.
=dateadd ("Y",-10, [due date])
Displays the date value one months before the Date field value.
=dateadd ("M", -1,date ())
Displays the number of days between date 1 and date 2.
=datediff ("D", [Order Date], [Ship Date])
A record from today's date up to three months.
Betweeb date () and Adddate (3,date ())
Calculate age by date of birth (birthday)
=iif (Date ())-month ([Birth date]) >-1,year (date ())-year ([Birth date]), year (date ())-year ([Birth date])-1)
Custom Date/time format (Format function)
(:) The time separator. In some areas, you might use other symbols as time separators. When formatting a time value, the time separator can be separated in time, minutes, and seconds. The real character of the time separator depends on the setup of the system when the format is exported.
(/) Date separator. In some areas, you might use other symbols as date separators. When formatting date values, the date separator can separate the year, month, and day. The true character of the date separator depends on the system settings when the format is exported.
C Displays the date in ddddd and displays the time in TTTTT. If you want to display a number with no decimal digits, only the date part is displayed, and if you want to display no integer portion of the value, only the time portion is displayed.
D
The day (1–31) is displayed with a number that does not have a leading zero.
Dd
Displays the day with a number with a leading zero (01–31).
Ddd
The day (SUN–SAT) is represented by shorthand.
dddd
The day is represented by the full name (Sunday–saturday).
Ddddd
Display in full date notation (including year, month, day), and date display depends on the system's short date format. The default short date format is m/d/yy.
Dddddd
Displays the number of date series (including year, month, day) in full date notation, and the date display depends on the long date format recognized by the system. The default long date format is MMMM dd, yyyy.
Aaaa
As with dddd, it is only a localized version of the string.
W
The date in the week is represented numerically (1 table Sunday ~ 7 table Saturday).
Ww
The week of the year is represented numerically (1–54).
M
Displays the month (1–12) with a number without a leading zero. If M is directly following H or HH, then the display will be divided rather than month.
Mm
Displays the month (01–12) with a leading zero number. If M is directly following H or HH, then the display will be divided rather than month.
Mmm
The month (JAN–DEC) is represented by shorthand.
Mmmm
The month is expressed in full name (January–december).
Oooo
As with mmmm, it is only a localized version of the string.
Q
The quarter of the year is expressed numerically (1–4).
Y
The day of the year is represented numerically (1–366).
Yy
The year is expressed in two digits (00–99).
yyyy
The year is expressed in four digits (00–99).
H
Displays an hour (0–23) with a number without a leading zero.
Hh
Displays the hour (00–23) with a leading zero number.
N
Displays the score with a number that has no leading zeros (0–59).
Nn
Displays the score (00–59) with a leading zero number.
S
Displays seconds (0–59) with numbers that do not have a leading zero.
Ss
Displays a second (00–59) with a leading zero number.
T T t t
Displays (including time, minutes, seconds) in full time notation, formatted with the time separator defined by the system-recognized time format. If you choose to have a leading 0 and the time is before 10:00 a.m. Or p.m, then a leading zero is displayed. The default time format is H:MM:SS.
am/pm
Use 12 hours in conjunction with capital AM symbol at noon and 11:59 p.m at noon and 12 hours with capital PM.
am/pm
Use 12 hours in conjunction with lowercase am symbols at noon and 11:59 p.m in 12 hours with lowercase pm.
a/p
Use 12 hours in conjunction with capital a symbol at noon and 11:59 p.m at noon and 12 hours with capital P.
a/p
Use 12 hours in conjunction with lowercase a symbol at noon and 11:59 p.m in 12 hours with lowercase P.
ampm
To be used 12 hours before noon with the system set of AM string literals, at noon and 11:59 p.m. Use the PM string literal with the system setup 12 hours. AMPM can be uppercase or lowercase, but must match your system settings. The default format is AM/PM.
Example of a Date function
Today's Date: =date ()
Day: =day (date)
Month: =month (date ())
Year: =year (date ())
Quarter: =datepart ("Q", Date ())
Capitalize the date
Function Date2chinese (Idate)
Dim Num (10)
Dim iyear
Dim Imonth
Dim Iday
num (0) = "0"
num (1) = "One"
num (2) = "Two"
num (3) = "three"
num (4) = "Four"
num (5) = "Five"
num (6) = "Six"
num (7) = "Seven"
num (8) = "Eight"
num (9) = "Nine"
Iyear = year (Idate)
Imonth = Month (Idate)
Iday = Day (Idate)
Date2chinese = num (iyear \ 1000) + _
Num (iyear) mod + num (iyear mod) + num (iyear mod 10) + "year"
If Imonth >= Then
If Imonth = Ten Then
Date2chinese = Date2chinese + "Ten" + "month"
Else
Date2chinese = Date2chinese + "ten" + num (imonth Mod 10) + "Month"
End If
Else
Date2chinese = Date2chinese +num (imonth Mod 10) + "Month"
End If
If Iday >= Then
If Iday = Ten Then
Date2chinese = Date2chinese + "Ten" + "Day"
ElseIf Iday = Or Iday = Then
Date2chinese = Date2chinese + num (iday \ 10) + "Ten" + "Day"
ElseIf iday > Then
Date2chinese = Date2chinese + num (iday \ 10) + "ten" + num (iday Mod 10) + "Day"
Else
Date2chinese = Date2chinese + "ten" + num (iday Mod 10) + "Day"
End If
Else
Date2chinese = Date2chinese + num (iday Mod 10) + "Day"
End If
End Function
Figure out the number of days per month
One law:
Dim A, B, c
A = Year (now ())
b = Month (now ())
c = Format ((A & "/" & B + 1 & "/1"), "######")-Format ((A & "/" & B & "/1"), "######"
Two methods:
DateDiff ("D", Format (date, "yyyy-mm-01"), Format (DATEADD ("M",-1, Date), "yyyy-mm-01")
DateDiff can calculate the difference between two dates a few days!
Three methods:
Day (DATEADD ("D",-1, Format (Date, "yyyy-mm-01"))
The day function can tell that a date is the day ordinal of the month, and we'll take the last of the month out for days! There's got to be a better way! For example, you can define an array, put the days of each month in, or write a function to calculate the number of days every one months as long as you consider the problem of the boudoir year!
How to get the first day of each month of a year is a week
Private Sub Command1_Click ()
Dim i As Integer, A As Integer, B As Integer, C as String
A = InputBox ("Please enter the year", "the first day of the month of the year is the Week")
Form1.cls
For i = 1 to 12
C = A & "-" & I & "-1"
B = Weekday (C)
Select Case B
Case Vbsunday
Print A & "Year" & I & "month 1st is Sunday"
Case Vbmonday
Print A & "Year" & I & "Month 1st is Monday"
Case Vbtuesday
Print A & "Year" & I & "month 1st is Tuesday"
Case Vbwednesday
Print A & "Year" & I & "month 1st is Wednesday"
Case Vbthursday
Print A & "Year" & I & "month 1st is Thursday"
Case Vbfriday
Print A & "Year" & I & "Month 1st is Friday"
Case Vbsaturday
Print A & "Year" & I & "Month 1st is Saturday"
End Select
Next I
End Sub
Count days and month-end dates
Function Days of the month (date as date) as Byte
Days of the month = DateSerial (year (date), Month (date) + 1, day (date))-date
End Function
Function month End (date as date) as Date
Month end = DateSerial (year (date), Month (date) + 1, 1)-1
End Function
Function month (date as date) as Date
month = date-day (date) + 1
End Function
The last day of this month is the week
SELECT
Weekday (DATEADD ("M", 1,dateserial (Date ()), Month (date ()), 1)-1) as the last day of the month is the week,
The last day of next month is the week
SELECT
Weekday (DATEADD ("M", 2,dateserial (Date ()), Month (date ()), 1)-1) as the last day of next month is the week,
The last week of the month 5 to the end of days
SELECT
(Weekday (DATEADD ("M", 1,dateserial (Date ()), Month (date ()), 1)-1)) +1) Mod 7 as the last week of the month 5 to the end of the days;
The last week of the next month 5 to the end of the days
SELECT
(Weekday (DATEADD ("M", 2,dateserial (Date ()), Month (date ()), 1)-1)) +1) Mod 7 as the last week of the next month 5 to the end of the days;
The last week of the month 5 of the date
SELECT
DateAdd ("M", 1,dateserial (Date ()), Month (date ()), 1)) (Weekday (DateAdd ("M", 1,dateserial (Date ()), Month (date ()), 1)-1) +1) Mod 7 as the last week of the month 5;
Date of the last week of next month 5
SELECT
DateAdd ("M", 2,dateserial (Date ()), Month (date ()), 1)) (Weekday (DateAdd ("M", 2,dateserial (Date ()), Month (date ()), 1)-1) +1) Mod 7 as the date of the last week of the next month 5;
More thinking, more innovation, is the right path!