Development log: Summary of asp SQL date operation statements

Lists the data of a day and a week.

Select   Distinct Dateandtime From Ctdate_by_query_date Where Dateandtime Between ((# 2006 - 5 - 15 # + (Weekday ( 2006 - 5 - 15 ) - 7 )) - 6 ) And (# 2006 - 5 - 15 # - 7 ) + Weekday ( 2006 - 5 - 15 )
SQL = " Select   Distinct Dateandtime From Ctarticle Where Dateandtime Between ((#" & Date & "# + (Weekday (" & Date & ")" & Norp & " 7 )) - 6 ) And (#" & Date & "#" & Norp & " 7 ) + Weekday (" & Date & ")"

Query the data of all days of a day in the week

Select   *   From Ctdate_by_query_date
Where Dateandtime Between ((# 2006 - 5 - 15 # + Weekday ( 2006 - 5 - 15 )) - 6 ) And # 2006 - 5 - 15 # + Weekday ( 2006 - 5 - 15 )

 ProgramCode

Select   *   From Ctdate_by_query_date Where Dateandtime Between # 2006 - 5 - 1 # And # 2006 - 5 - 30 #

Lists the years of different years, and do not use the same

Select   Distinct   Year (Dateandtime) From Ctarticle

Example:
Expr1000
2000
2003
2004
2005
2006

List the first record of a day and the previous year

Select   Top   1 Dateandtime From Ctarticle Where   Year (Dateandtime) = ( 2006 ) - 1  
SQL = " Select   Top   1 Dateandtime From Ctarticle Where   Year (Dateandtime) = ( Year (#" & Date & "#))" & Norp

List data of a year

SQL = " Select   *   From Ctdate_by_query_date Where   Year (Dateandtime) = " & Year (Rqqdt _) & " Order   By Dateandtime Desc "

Search for the first record in January

 

Select   Top   1 Dateandtime From Ctarticle Where   Year (Dateandtime) = Year (# 2006 - 5 - 28 #) And Month (Dateandtime) = Month (# 2006 - 5 - 28 #) - 1  

List data of a month in January

SQL = " Select   *   From Ctdate_by_query_date Where   Year (Dateandtime) = " & Year (Rqqdt _) & " And   Month (Dateandtime) = " & Month (Rqqdt _) & " Order   By Dateandtime Desc "

List data of a day

SQL = " Select   *   From Ctdate_by_query_date Where Dateandtime = #" & Rqqdt _ & "# Order   By A. ArticleID Desc "

Time and date

Example 1: List data of the current day

SQL = " Select   *   From Ctdate_by_query_date Where Dateandtime = Date () Order   By A. ArticleID Desc "