Date and time functions are useful for building a site. The owner of the site is often interested in when the data in a table is updated. With date and time functions, you can track changes in a table at the millisecond level.
Returns the current date and time
Through the function getdate (), you can get the current date and time. For example, the statement select GETDATE () returns the following result:
...................................
NOV 1997 3:29AM
(1 row (s) affected)
Obviously, if you use this function in the future, you will get the date later than this time, or stem early.
The function getdate () can be used as the default value for the Datedime () Type field. This is useful for saving the time when the record was inserted. For example, suppose you have a table to hold the activity log on your site. Whenever a visitor visits your site, add a new record to the table, noting the visitor's name, activity, and time of the visit. 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
As you may have noticed, in the example in the previous section, 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 (). Table 11.1 shows all of the formats.
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 of the formats listed in Table 11.1, you can add the type value plus 100来 to show the century (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. For example, suppose you want to list the month in which each site in your site directory is queried. At this time you do not want the full date and time to mess up the Web page. 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:
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 table 11.2.
Table 11.2 The various parts of the 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 ():
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/20000"
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.
Attention:
In the discussion in this section, assume that the field entrydate is a datetime, not a smalldatetime type. The discussion in this section is also true for smalldatetime fields, although smalldatetime fields can only be accurate to seconds.
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:
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. For example, suppose a visitor must register before you can use your site. After registering, they can use your site for free for one months. To determine when their free time will run out, you can use the following SELECT statement:
The function DateAdd () has a parameter of three variables. The first variable represents a part of the date (see table 11.2), 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:
Contrary to what you expect, use the function DateAdd () to add a date to one months, and it does not add 30 days. This function simply adds the month value to 1. This means that people who register in November will get 2 or 3 days more than those registered in February. To avoid this problem, you can use the function DateAdd () to increase the number of days directly, not the month.
Send mail
You can use SQL Sever to send simple e_mail information. To do this, you need to install mail servers in your system, such as Microsoft Exchange Sever (see Chapter Fourth "Exchange Active Sever,index Sever, and NetShow"). You also need to configure SQL Sever to identify the mail server.
To enable SQL Sever to identify the mail server, start the transaction manager and select sever| from the menu SQL mail| Configue, a dialog box like the one shown in Figure 11.3 appears. Enter the username and password you registered in the mail server, and then click OK.
Attention:
If you use Microsoft Exchange Sever, the process of configuring SQL Sever will be significantly different. You need to run Microsoft SQL Sever and Exchange Sever under the same (domain) user account. You also need to install Exchange Cliect on a machine with SQL Sever installed and create a profile for this account. Once you have done this, you can enter the name of the profile in the SQL Mail Configuration dialog box.
Figure 11. 3
Before you send a message, you start SQL Mail first. Choose sever| from the menu SQL mail| Start. If your mail server is configured correctly and you enter the correct username and password, SQL Mail will start successfully.
Attention:
You can configure the SQL sever to start the mail service automatically. To do this, in the Set Sever optons dialog box (select sever| from the menu) SQL sever| Configure), select Auto Start Mail client.
To send a message, you can use an extended stored procedure named xp_sendmail. Here is an example of how to use this process:
This process call sends a simple email message to the E_Mail address president@whitehouse.gov: "Hello Mr President". You can use any other email address and information to replace the corresponding content in the above example, but the information you send cannot exceed 255 characters in length.
Stored procedures xp_sendmail are useful when you want to keep abreast of the state of your site database. For example, you can send information to a page management program. If there's something wrong with your site, you can know it right away. The next chapter will cover more on the contents of the stored procedure.
Summarize
This chapter deepens your knowledge of SQL. You've learned how to index and make your queries faster. You also learned how to insert, delete, and update data in a table, and how to use aggregate functions to get statistical information about the data in a table. Finally, you learn a lot of valuable expressions, functions, and processes that manipulate strings, dates and times, and emails.
The next chapter will further deepen your mastery of Microsoft SQL Sever. You will learn how to design with SQL, how to set up stored procedures, triggers, and execution plans. More exciting, you'll learn a simple way to get SQL Sever to create Web pages automatically.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
