Access Date and time function Rollup _access

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!