Summary of Access Date and Time Functions

When developing a system today, we need to implement such a function.

Query related content based on the selected date, but not filter by the entire date, but filter by year, month, or day splitting, for example, from January 1, 2013 to January 1, February.

In sqlserver, we can directly perform the task 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

However, access does not work, because there are some different methods, you need to write

Select * from pays where 1 = 1 and datepart ("yyyy", pdate) = 2013 and datepart ("m", pdate) = 2 and datepart ('D', pdate) = 21 order by pdate

The SQL Server functions are mentioned earlier. Now let's summarize the date functions of access.

How to convert text: 2003.08.04 to date: 2003-08-04
Cdate (replace ("2003.08.04 ",".","-"))

Displays the day of the week of the year for the current date.
= Format (Now (), "ww ")

Ww is 1 to 53.

Displays the four-digit year value of the date field value.
= DatePart ("yyyy", [order date])

Displays the date value of the first 10 days of the date field value.
= DateAdd ("y",-10, [due date])

Displays the date value of the previous month of the date field value.
= DateAdd ("m",-1, Date ())

Displays the number of days between date 1 and date 2.
= DateDiff ("d", [date of order], [date of delivery])

From today to three months later.
Betweeb date () and adddate (3, date ())

Calculate the age (AGE) based on the date of birth)
= IIf (Month (Date ()-Month ([Date of Birth])>-1, Year (Date ()-Year ([Date of Birth]), year (Date ()-Year ([Date of Birth])-1)

Custom date/time Format (Format function)
(:) Time separator. In some regions, other symbols may be used as time delimiters. When formatting the time value, the time separator can be separated by hour, minute, and second. The real characters of the time separator are determined by the system settings when the format is output.

(/) Date separator. In some regions, other symbols may be used as the date separator. When formatting a date value, the date separator can be used to separate the year, month, and day. The real characters of the date separator are determined by the system settings when the format is output.

C uses ddddd to display the date and ttttt to display the time. If the value to be displayed has no decimal part, only the date part is displayed. If the value to be displayed has no integer part, only the time part is displayed.

D

Display the Day (1-31) with no leading zero ).

Dd

Display the day (01-31) with a leading zero ).

Ddd

Sun-Sat ).

Dddd

The full name indicates Sunday-Saturday ).

Ddddd

It is displayed in full date notation (including year, month, and day). The display of date depends on the system's short date format settings. The default short date format is m/d/yy.

Dddddd

Display the number of date series (including year, month, and day) in full date notation. The display of date depends on the long Date Format recognized by the system. The default long date format is mmmm dd, yyyy.

Aaaa

Like dddd, it is only the localized version of the string.

W

Represent the date in a week in numerical values (1 Table Sunday ~ 7 Table Saturday ).

Ww

The week in a year is expressed as a numerical value (1-54 ).

M

The month (1-12) is displayed with no leading zero ). If m is directly following h or hh, it will be displayed in minutes rather than months.

Mm

Displays the month (01-12) with a leading zero ). If m is directly following h or hh, it will be displayed in minutes rather than months.

Mmm

Month (Jan-Dec ).

Mmmm

January-December ).

Oooo

Like mmmm, it is only the localized version of the string.

Q

Represent the quarter of a year in numbers (1-4 ).

Y

The day of a year is expressed as a numerical value (1-366 ).

Yy

Year (00-99) in double digits ).

Yyyy

Year (00-99) in four digits ).

H

The hour is displayed with no leading zero (0-23 ).

Hh

Displays the hour (00-23) with a leading zero ).

N

Displays the score (0-59) with no leading zero ).

Nn

Displays the score (00-59) with a leading zero ).

S

Display seconds (0-59) with no leading zero ).

Ss

Display seconds (00-59) with a leading zero ).

T

It is displayed in full time notation (including hour, minute, and second) and formatted using the time separator defined in the time format recognized by the system. If you select to have a leading zero and the time is before a.m. or p.m., the time with leading zero is displayed. The default time format is h: mm: ss.

AM/PM

Use the uppercase ampersand (12 hours) before noon and the uppercase PM (12 hours) between noon and p.m.

Am/pm

Use the lower-case am symbol with 12 hours before noon and 12 hours with lower-case pm between noon and 11: 59 p.m.

A/P

Use the uppercase A symbol with 12 hours before noon, and use the uppercase P symbol with 12 hours between noon and 11: 59 p.m.

A/p

Use it with the lowercase a symbol 12 hours before noon, and use it with the lowercase P symbol 12 hours between noon and 11: 59 p.m.

AMPM

Use the AM string text set by the system 12 hours before noon, and use the PM string text set by the system 12 hours between noon and 11: 59 p.m. AMPM can be in upper or lower case, but must match your system settings. The default format is AM/PM.

Date function example
Day Date: = Date ()

Day: = Day (date)

Month: = Month (date ())

Current Year: = Year (date ())

Quarter: = DatePart ("q", Date ())

Capital date
Function Date2Chinese (iDate)

Dim num (10)

Dim iYear

Dim iMonth

Dim iDay

Num (0) = "〇"

Num (1) = "1"

Num (2) = "2"

Num (3) = "3"

Num (4) = "4"

Num (5) = "5"

Num (6) = "6"

Num (7) = "7"

Num (8) = "8"

Num (9) = "9"

IYear = Year (iDate)

IMonth = Month (iDate)

IDay = Day (iDate)

Date2Chinese = num (iYear/1000) + _

Num (iYear \ 100) Mod 10) + num (iYear \ 10) Mod 10) + num (iYear Mod 10) + "year"

If iMonth> = 10 Then

If iMonth = 10 Then

Date2Chinese = Date2Chinese + "10" + "month"

Else

Date2Chinese = Date2Chinese + "10" + num (iMonth Mod 10) + "month"

End If

Else

Date2Chinese = Date2Chinese + num (iMonth Mod 10) + "month"

End If

If iDay> = 10 Then

If iDay = 10 Then

Date2Chinese = Date2Chinese + "10" + "day"

ElseIf iDay = 20 Or iDay = 30 Then

Date2Chinese = Date2Chinese + num (iDay \ 10) + "10" + "day"

ElseIf iDay> 20 Then

Date2Chinese = Date2Chinese + num (iDay \ 10) + "10" + num (iDay Mod 10) + "day"

Else

Date2Chinese = Date2Chinese + "10" + num (iDay Mod 10) + "day"

End If

Else

Date2Chinese = Date2Chinese + num (iDay Mod 10) + "day"

End If

End Function

Calculate the number of days per month
Method 1:

Dim a, B, c

A = Year (Now ())

B = Month (Now ())

C = Format (a & "/" & B + 1 & "/1 "),"######") -Format (a & "/" & B & "/1 "),"######")

Method 2:

DateDiff ("d", Format (Date, "yyyy-mm-01"), Format (DateAdd ("m",-1, Date), "yyyy-mm-01 "))

DateDiff can calculate the difference between two dates for several days!

Method 3:

Day (DateAdd ("d",-1, Format (Date, "yyyy-mm-01 ")))

The day function knows that a date is the DAY of the month. Let's take out the last day of the month as DAY! There should be a better way! For example, you can define an array, put the day of each month, or write a function to calculate the number of days of each month. You just need to consider the issue of the year of expiration!

How to get the day of a week on the first day of each month
Private Sub commandementclick ()

Dim I As Integer, A As Integer, B As Integer, C As String

A = InputBox ("Enter the year", "the first day of each month in A year is the day of 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 & "the first day of the month is Sunday"

Case vbMonday

Print A & "year" & I & "Monday"

Case vbTuesday

Print A & "year" & I & "the first day of the month is Tuesday"

Case vbWednesday

Print A & "year" & I & "the first day of the month is Wednesday"

Case vbThursday

Print A & "year" & I & "the first day of the month is Thursday"

Case vbFriday

Print A & "year" & I & "the first day of the month is Friday"

Case vbSaturday

Print A & "year" & I & "the first day of the month is Saturday"

End Select

Next I

End Sub

Calculates the number of days and the end date of the month at the beginning of the month.
Function Number of days this month (Date As Date) As Byte

Days of this Month = DateSerial (Year (date), Month (date) + 1, Day (date)-date

End Function

Function end of month (Date As Date) As Date

End of Month = DateSerial (Year (date), Month (date) + 1, 1)-1

End Function

Function month (Date As Date) As Date

Beginning of month = date-Day (date) + 1

End Function

The last day of this month is the day of the week
SELECT

Weekday (DateAdd ("m", 1, DateSerial (Year (Date (), Month (Date (), 1)-1) AS the last day of the Month is the day of the week,

The last day of next month is the day of the week
SELECT

Weekday (DateAdd ("m", 2, DateSerial (Year (Date (), Month (Date (), 1)-1) AS the last day of the next Month is the day of the week,

Number of days from the last week of the month to the end of the month
SELECT

(Weekday (DateAdd ("m", 1, DateSerial (Year (Date (), Month (Date (), 1)-1) + 1) mod 7 AS the number of days from the last week of the month to the end of the month;

Number of days from the last week of the next month to the end of the month
SELECT

(Weekday (DateAdd ("m", 2, DateSerial (Year (Date (), Month (Date (), 1)-1) + 1) mod 7 AS the number of days from the last week of the next month to the end of the month;

The date of the last week 5 of this month
SELECT

DateAdd ("m", 1, DateSerial (Year (Date (), Month (Date (), 1)-1-(Weekday (DateAdd ("m ", 1, DateSerial (Year (Date (), Month (Date (), 1)-1) + 1) Mod 7 AS the Date of the last week of the Month;

The date of the last week 5 next month
SELECT

DateAdd ("m", 2, DateSerial (Year (Date (), Month (Date (), 1)-1-(Weekday (DateAdd ("m ", 2, DateSerial (Year (Date (), Month (Date (), 1)-1) + 1) Mod 7 AS the Date of the last week of next Month;

Think more and innovate more!