SQL statement Get date

　　SQL statement gets a specific date

1. The first day of one months

Select DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

2. Monday of this week

Select DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

3. The first day of the year

Select DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

4. First day of the quarter

Select DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

5. The middle of the day

Select DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

6. Last day of last month

Select DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

7. Last day of last year

Select DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

8. Last day of the month

Select DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

9. The last day of the year

Select DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0)

10. The first Monday of this month

Select DATEADD (wk, DATEDIFF (Wk,0,dateadd Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

　　Returns the current date and time

Through the function getdate (), you can get the current date and time. The function getdate () can be used as the default value for a Datedime type field. This is useful for saving the time when the record was inserted. To create a table with records that contain the current date and time, you can add a datetime field that specifies its default value as the return value of the function GETDATE (), like this:

Create TABLE Site_log (

Username VARCHAR (40),

Useractivity VARCHAR (100),

EntryDate DATETIME DEFAULT GETDATE ())

　　Convert Date and time

The return value of the function getdate () is displayed only to seconds. In fact, the internal time of the SQL sever can be accurate to the millisecond level (and, to be precise, to 3.33 milliseconds).

To get a different format date and time, you need to use the function convert (). For example, when the following statement executes, the time displayed will include milliseconds:

Select CONVERT (VARCHAR (), GETDATE (), 9)

Note the use of the number 9 in the example. This number indicates which date and time format to use when displaying the date and time. When this statement is executed, the following date and time are displayed:

Nov 1997 3:29:55:170AM

(1 row (s) affected)

You can use a number of different styles of date and time formats in the function convert (). The following table shows all the formats.

　　Type of date and time:

Type value standard output

0 Default Mon dd yyyy hh:miam

1 USA mm/dd/yy

2 ANSI yy.mm.dd

3 British/french Dd/mm/yy

4 German dd.mm.yy

5 Italian Dd-mm-yy

6-dd Mon yy

7-mon Dd,yy

8-hh:mi:ss

9 Default + milliseconds--mon DD yyyy

Hh:mi:ss:mmmAM (OR)

USA Mm-dd-yy

JAPAN YY/MM/DD

ISO YYMMDD

Europe Default + milliseconds--dd mon yyyy

Hh:mi:ss:mmm (24h)

14-hh:mi:ss:mmm (24h)

Types 0, 9, and 13 always return a four-digit year. For other types, to show the century, add the style value to 100. Types 13 and 14 return 24 hours of clock time. The months returned by the type 0, 7, and 13 are represented by three-bit characters (Nov represents November).

For each format listed in the table, you can add the type value plus 100来 to show the year (for example, 00 will show 2000). For example, to display dates by Japanese standards, including centuries, you should use the following statement:

Select CONVERT (VARCHAR (), GETDATE (), 111)

In this example, the function convert () converts the date format to show the 1997/11/30

　　Extract date and time

In many cases, you may just want to get a part of the date and time, not the full date and time. To extract a specific part of the date, you can use the function datepart (), like this:

Select site_name ' site name ',

DATEPART (mm,site_entrydate) ' Month Posted ' from site_directory

The parameter of the function datepart () is two variables. The first variable specifies which part of the date to extract, and the second variable is the actual data. In this example, the function datepart () extracts the month, because MM represents the month. The following is the output of this SELECT statement:

Site Name Month Posted

........................................................................

Yahoo 2

Microsoft 5

MAGICW3 5

(3 row (s) affected)

The Month posted column shows the month in which each site is queried. The return value of the function datepart () is an integer. You can use this function to extract different parts of the date, as shown in the following table.

　　The parts of a date and their abbreviations

Date part Shorthand

Year YY 1753--9999

Quarter QQ 1--4

month mm 1--12

Day of the year dy 1--366

Day DD 1--31

Week wk 1--53

Weekday DW 1--7 (Sunday--saturday)

Hour HH 0--23

Minute Mi 0--59

Second SS 0--59

Milisecond Ms 0--999

When you need to compare dates and times, it is useful to use the function datepart () to return an integer. However, the query result (2,5) in the example above is not very readable. To get part of the date and time in a more readable format, you can use the function datename (), as shown in the following example:

Select site_name ' site name '

Datename (mm,site_entrydate) ' Month Posted '

From Site_directory

function datename () and function datepart () receive the same parameters. However, its return value is a string, not an integer. The following is the result of the previous example using Datename ():

Site Name Month Postec

............................................................................

Yahoo February

Microsoft June

MAGICW3 June

(3 row (s) affected)

You can also use the function Datenae () to extract one day of the one week. The following example extracts the month of one day and date of the week:

Select site_name ' site name ',

Datename (dw,site_entrydate) + '-' + datename (mm,site_entrydate)

' Day and Month Posted ' FORM site_directory

When this example is executed, the following results are returned:

Site Name Day and Month Posted

.................................................................................

Yahoo friday-february

Microsoft Tuesday-june

MAGICW3 Monday-june

(3 row (s) affected)

　　Return date and Time range

When you analyze the data in a table, you may want to take out data at a particular time. You may be interested in a particular day--for example, December 25, 2000--of a visitor's activity on your site. To remove this type of data, you might try to use a SELECT statement like this:

SELECT * FROM Weblog Where entrydate= "12/25/2000"

Don't do that. This SELECT statement does not return the correct record-it will only return the date and time is the 12/25/2000 12:00:00:000am record. In other words, only records entered exactly 0 o'clock midnight are returned.

The problem is that SQL Sever will replace part of the date and time with the full date and time. For example, when you enter a date but do not enter a time, the SQL sever will add the default time "12:00:00:000am". When you enter a time but do not enter a date, SQL sever will add the default date "1 1900".

To return the correct record, you need to apply the date and time range. There are more than one way to do this. For example, the following SELECT statement will return the correct record:

Select * FROM weblog

Where entrydate>= "12/25/2000" and entrydate< "12/26/2000"

This statement completes the task because it selects a record in the table that is greater than or equal to 12/25/2000 12:00:00:000am and is less than 12/26/2000 12:00:00:000am. In other words, it will correctly return every record entered in the 2000 Christmas Day.

Alternatively, you can use like to return the correct record. By including the wildcard character "%" in the date expression, you can match all the time for a specific date. Here's an example:

Select * from weblog Where entrydate like ' Dec 25 2000% '

This statement can match the correct record. Because the wildcard character "%" represents any time.

Using these two types of functions that match the date and time range, you can choose a month, a day, a year, an hour, a minute, a second, or even a millisecond to enter a record. However, if you use like to match seconds or milliseconds, you first need to convert the date and time to a more precise format (see the "Convert Date and Time" section above) using the function convert ().

　　Compare dates and times

Finally, there are two date and time functions that are useful for fetching records based on date and time. Using Functions DateAdd () and DateDiff (), you can compare the dates of the morning and evening. For example, the following SELECT statement shows how many hours each record in a table has been entered:

Select entrydate ' time entered '

DATEDIFF (Hh,entrydate,getdate ()) ' Hours Ago ' from weblog

If the current time is November 30, 2000 6:15 P.M., the following results are returned:

Time entered Hours Ago

.........................................................

Dec 4:09pm 2

Dec 4:13pm 2

Dec 1 4:09pm 698

(3 row (s) affected)

The parameter of the function Dadediff () is three variables. The first variable specifies a part of the date. In this example, the date is compared by the hour, (see table 11.2 for details of each part of the date, for 689 hours between the specified time of November 1, 2000 and November 30, 2000). The other two parameters are the time to compare. In order to return a positive number, the earlier time should be given first.

The function DateAdd () adds two dates. This function is useful when you need to compute data such as the cutoff date. If you want to check the records of registered users one months ago, you can use the following SELECT statement:

Select username ' User Name ',

DATEADD (mm,1,firstvisit_date) ' Registration Expires '

From registration_table

The function DateAdd () has a parameter of three variables. The first variable represents a part of the date, which is used to represent the month's mm. The second variable specifies the interval of time--in this case, one months. The last variable is a date, in this case, the date is taken from the DateTime field firstvisit_date. Assuming the current date is June 30,2000, this statement returns the following:

User Name Registration Expires

.......................................................................................

Bill Gates 4:09pm

President Clinton June 4:13pm

William Shakespeare June 1 4:09pm

(3 row (s) affected)

　　Attention:

Use the function DateAdd () to add a date to one months, it does not add 30 days. This function simply adds the month value to 1.