Basics of SQL Data Operations (Intermediate) 10

Manipulating Date and Time

Date and time functions are very useful for building a website. Site owners are often interested in when data in a table is updated. With date and time functions, you can track changes to a table at the millisecond level.

Return 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 results:

…………………………………………..

NOV 30 1997 3:29AM

(1 row(s) affected)

Obviously, if you use this function in the future, the date you get will be later than this time, or earlier.

The function GETDATE() can be used as the default value of DATEDIME() type field. This is useful for saving the current time when inserting a record. For example, suppose you have a table that holds a log of activity on your site. Each time a visitor visits your site, add a new record to the table, recording the visitor's name, activity, and time of visit. To create a table whose records contain the current date and time, you can add a DATETIME field and specify that its default value is the return value of the GETDATE() function, like this:

CREATE TABLE site_log (

username VARCHAR(40),

useractivity VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())

Convert date and time

You may have noticed that in the example in the previous section, the return value of the function GETDATE() is only displayed to the second. In fact, SQL Sever internal time can be accurate to the millisecond level (to be precise, it can be accurate to 3.33 milliseconds).

To get date and time in different formats, you need to use the function CONVERT(). For example, when the following statement is executed, the displayed time will include milliseconds:

SELECT CONVERT(VARCHAR(30),GETDATE(),9)

Note the use of the number 9 in the example. This number specifies which date and time format to use when displaying dates and times. When this statement is executed, the following date and time will be displayed:

…………………………………………..

Nov 30 1997 3:29:55:170AM

(1 row(s) affected)

In the function CONVERT() you can use many different styles of date and time formats. Table 11.1 shows all formats.

Table 11.1 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

Default + milliseconds--mon dd yyyy
hh:mi:ss:mmmAM(or )

10 USA mm-dd-yy

11 JAPAN yy/mm/dd

12 ISO yymmdd

13 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 display the century, add 100 to the style value. Types 13 and 14 return the time on a 24-hour clock. Types 0, 7, and 13 return the month as a three-character representation (use Nov for November).

For each of the formats listed in Table 11.1, you can add 100 to the type value to display the year with centuries. (For example, the year 00 will appear as the year 2000). For example, to display the date according to Japanese standards, including the century, you would use the following statement:

SELECT CONVERT(VARCHAR(30), GETDATE(), 111)

In this example, the function CONVERT() converts the date format and displays it as 1997/11/30

Extract the date and time

In many cases, You may want to get only part of the date and time, rather than the complete date and time. For example, suppose you want to list the months in which each site in your site directory was queried. At this point you don't want the full date and time to clutter the page. To extract a specific part of a date, you can use the function DATEPART(), like this:

SELECT site_name ‘Site Name’,

DATEPART(mm,site_entrydate) ‘Month Posted’ FROM The parameters of site_directory

function DATEPART() are two variables. The first variable specifies which part of the date to extract; the second variable is the actual data. In this example, the function DATEPART() extracts the month, since mm represents the month. Below is this SELECT The output result of the statement:

Site Name Month Posted

………………………………………………………………

Yahoo 2

Microsoft 5

Magicw3 5

(3 row(s) affected)

Month The Posted column shows the month each site was queried. The return value of function DATEPART() is an integer. You can use this function to extract various parts of a date, as shown in Table 11.2.

Table 11.2 Parts of date and their abbreviations

Date part abbreviation value

year yy 1753--9999

quarter QQ 1--4

month mm 1--12

day of 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 results (2, 5) in the above example are not very readable. To get a partial 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 obtained by using DATENAME() in the above example:

Site Name Month Postec

…………………………………………………………………………………….

Yahoo February

Microsoft June

Magicw3 June

(3 row(s) affected)

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

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 will be 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 the table, you may want to retrieve the data for a specific time. You may be interested in visitor activity on your site on a particular day - say, December 25, 2000. To retrieve 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 this. This SELECT statement will not return the correct record - it will only return the date and time of 12/25/2000 Record of 12:00:00:000AM. In other words, only records entered at exactly midnight are returned.

Note:

In the discussion in this section, it is assumed that the field entrydate is of type DATETIME, not SMALLDATETIME. The discussion in this section is also applicable to SMALLDATETIME type fields, but SMALLDATETIME type fields can only be accurate to seconds.

The problem is SQL Sever will replace partial dates and times with complete dates and times. For example, when you enter a date but not a time, SQL Sever will add the default time "12:00:00:000AM". When you enter a time but not a date, SQL Server will add the default date "Jan 1 1900".

To return the correct records you need to apply a date and time range. There is more than one way to do this. For example, this SELECT below The statement will return the correct records:

SELECT * FROM weblog

WHERE entrydate>=”12/25/2000” AND entrydate<”12/26/2000”

This statement can complete the task because it selects the date and time in the table that are greater than or equal to 12/25/2000 12:00:00:000AM and less than 12/26/2000 Record of 12:00:00:000AM. In other words, it will correctly return every record entered on Christmas Day 2000.

Alternatively, you can use LIKE to return the correct record. You can match all times on a specific date by including the wildcard character "%" in the date expression. Here's an example:

SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’

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

Using these two functions for matching date and time range, you can select records entered within a certain month, day, year, hour, minute, second, or even millisecond. But if you use LIKE To match seconds or milliseconds, you first need to convert the date and time to a more precise format using the function CONVERT() (see the previous section "Converting Dates and Times").

Comparing dates and times

Finally, there are two date and time functions that are useful for taking out records based on date and time. Using the functions DATEADD() and DATEDIFF(), you can compare earlier and later dates. For example, the following SELECT statement will display how many hours each record in the table has been entered:

SELECT entrydate ‘Time Entered’

DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog

If the current time is 6:15 pm on November 30, 2000, the following results will be returned:

Time Entered Hours Ago

……………………………………………………………….

Dec 30 2000 4:09PM 2

Dec 30 2000 4:13PM 2

Dec 1 2000 4:09PM 698

(3 row(s) affected)

The parameters of the function DADEDIFF() are three variables. The variable specifies a part of the date. In this example, dates are compared on an hourly basis, (for details on the date components, refer to Table 11.2). There are 689 times between the specified times on the dates November 1, 2000, and November 30, 2000. Hours. The other two parameters are the times to be compared. 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 calculate data such as deadlines. For example, suppose visitors must register before they can use your site. After signing up, they can use your site for free for a month. To determine when their free time will run out, you can use a SELECT statement like this:

SELECT username ‘User Name’,

DATEADD(mm,1,firstvisit_date) ‘Registration Expires’

FROM The parameters of registration_table

function DATEADD() have three variables. The first variable represents a part of the date (see Table 11.2). In this example, mm represents the month. The second variable specifies the time interval - in this case, one month. The last variable is a date. In this example, the date is taken from the DATETIME type field firstvisit_date. Assume that the current date is June 30,2000, this statement will return the following content:

User Name Registration Expires

……………………………………………………………………………………

Bill Gates Jul 30 2000 4:09PM

President Clinton Jul 30 2000 4:13PM

William Shakespeare Jul 1 2000 4:09PM

(3 row(s) affected)

Note:

Contrary to what you might expect, using the function DATEADD() to add a month to a date does not add 30 days. This function simply adds 1 to the month value. This means that someone who signed up in November will get 2 or 3 more days than someone who signed up in February. To avoid this problem, you can use the function DATEADD() to directly add days instead of months.

Send email

You can use SQL Sever sends simple e_mail messages. To do this you need to have a mail server installed on your system such as Microsoft Exchange Sever (see Chapter 4, "Exchange Active Sever, Index Sever, and NetShow"). You also need to configure SQL Server to identify the mail server.

To make SQL Sever recognize the mail server, start the transaction manager and select Sever|SQL from the menu Mail|Configue, a dialog box as shown in Figure 11.3 will appear. Enter the username and password you registered with the mail server and click OK.

Note:

If you use Microsoft Exchange Sever, the process of configuring SQL Sever will be significantly different. You need to run Microsoft SQL under the same (domain) user account Sever and Exchange Sever. You also need to install Exchange on the machine where SQL Sever is installed Click and create a configuration file for this account. After completing this, you can use SQL Mail Enter the name of the configuration file in the Configuration dialog box.

Figure 11. 3

Before sending the email, you need to start SQL first Mail. Select Sever|SQL Mail|Start from the menu. If your mail server is configured correctly and you entered the correct username and password, SQL Mail will start successfully.

Note:

You can configure SQL Sever to automatically start the email service. To do this, in Set Sever Select Auto Start Mail in the Optons dialog box (select Sever|SQL Sever|Configure from the menu) Just Client.

To send an email, you can use an extended stored procedure called xp_sendmail. Here's an example of how to use this process:

master..xp_sendmail "president@whitehouse.gov","Hello Mr. President"

This procedure calls to send a simple email message to the e_mail address president@whitehouse.gov: "Hello Mr. President". You can replace the corresponding content in the above example with any other email address and information, but the information you send cannot exceed 255 characters in length.

When you want to know the status of your site database at any time, The stored procedure xp_sendmail is useful. For example, you can send a message to a page management program. If something goes wrong with your site, you will know more about stored procedures in the next chapter. Summary

This chapter deepens your SQL knowledge. You learned how to create indexes to 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 a Statistical information of the data in the table. Finally, you learned many valuable expressions, functions and procedures for manipulating strings, dates, times and emails

The next chapter will further deepen your understanding of Microsoft. Mastery of SQL Server. You will learn how to program with SQL and how to create stored procedures, triggers and execution plans. Even more exciting is that you will learn how to use SQL Sever is a simple way to automatically create web pages.

The above is the content of SQL Data Operation Basics (Intermediate) 10. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!