Basic operation of SQL data (time and date)
Date and time functions are very useful for establishing a site. The owner of the site is often when the data in a table is
Update interested. With the date and time function, you can track a change in milliseconds.
Return the current date and time
With the function getdate (), you can get the current date and time. For example, statement select getdate ()
Returns the following results:
.................................
NOV 30 1997 3:29 AM
(1 row (s) affected)
Obviously, if you use this function in the future, the date you get is later than this time, or earlier.
Function getdate () can be used as a default value of a Datedime () field. This is saved when inserting records
The time is useful at the time. For example, assume that there is a table to save the activity log on your site. Whenever there is an interview
When you ask your site, add a new record in the table, write down the visitor's name, activity, and visit.
Asked time. To create a table, where the record contains the current date and time, you can add a DateTime
Type field, specify its default value of the return value of getDate (), like this:
Create Table Site_log
Username varchar (40),
UserActivity varchar (100),
EntryDate DateTime Default getdate ())
Conversion date and time
You may have noticed that the return value of the function getDate () is displayed only in seconds. In fact, SQL Sever
The internal time can be accurate to millisecond (exactly, it can be accurate to 3.33 milliseconds).
To get the date and time of different formats, you need to use the function control (). For example, when this statement
When executed, the time displayed will include milliseconds:
Select Convert (VARCHAR (30), Getdate (), 9)
Note The use of numbers 9 in the example. This figure indicates which date and time format that uses time when displaying the date and time.
When this statement is executed, the following date and time are displayed:
........................................
NOV 30 1997 3: 29: 55: 170AM
(1 row (s) affected)
In the function control (), you can use many different styles of date and time format. The table below shows the place
Some format.
Table date and time type
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 four years. For other types, to display the century, add the style value to 100. Types 13 and 14 returns a 24-hour clock time. Type 0, 7, and 13, the month returned with three characters (with NOV)
Representing November).
For each format listed in the above table, you can add the type value to 100 to display an aged year (for example, 00 years
Will be shown as 2000). For example, you should display the date according to Japanese standard, including the century, you should use the following statement:
Select Convert (VARCHAR (30), Getdate (), 111)
In this example, the function convert () converts the date format and is displayed as 1997/11/30.
Extract date and time
In many cases, you may only want to get a part of the date and time, not a complete date and time. E.g,
Suppose you want to list the months that each site is queried in your site directory. At this point you don't want the full date and time
The web page is messy. To extract the 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 to extract the date; the second
Variables are actual data. In this example, the function datepart () extracts the month because the MM represents the month.
Here 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 that each site is queried. The return value of the function datepart () is an integer.
You can use this function to extract the different parts of the date, as shown in the following table.
Date of the table and its own abbreviation
Date partial shorthand 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
Use function datepart () returns an integer when you need to perform a date and time comparison. but,
The query result (2, 5) in the above example is not very easy to read. To get partial dates and time you have to read more.
To use the function datename (), as shown in the following example:
SELECT Site_name 'Site Name'
Datename (mm, site_entrydate) 'Month Posted'
From site_directory
Functions datename () and function datepart () receive the same parameters. However, its return value is one
String instead of an integer. Here is the result of the above example to use DateName (): Site Name Month Postec
..................................................................
Yahoo February
Microsoft June
Magicw3 June
(3 row (s) affected)
You can also use the function datenae () to draw a day in a week. The following example simultaneously extracts one
One day and date in 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 result is 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 might want to take out the data of a certain time. You may be a certain day
Middle - For example, December 25, 2000 - Visitors interested in the event of your site. To take out this type
Data, you may try to use this SELECT statement:
Select * from Weblog where entdate = "12/25/20000"
Do not do this. This SELECT statement does not return the correct record - it will return only the date and time is
12/25/2000 12: 00: 00: 000AM record. In other words, only records just entered at midnight zero are returned.
note:
In this article, assume that the field entrydate is a DateTime type, not a SmallDateTime.
The discussion of this section is also applicable to the SmallDateTime field, but the SmallDateTime type field is only
Can be accurate to second.
The problem is that SQL Sever will replace part and time with a complete date and time. For example, when you enter a date,
But when no time is input, SQL Sever will add the default time "12: 00: 00: 000AM". When you enter one
SQL Sever will add the default date "Jan 1 1900" when not entering the date.
To return to the correct record, you need to apply the date and time range. There is more than one way to do this. E.g,
The following SELECT statement will be able to return the correct record:
Select * from Weblog
Where entrydate> = "12/25/2000" and entrydate <"12/26/2000"
This statement can complete the task, because it is selected for the date and time in the table, greater than or equal to 12/25/2000
12: 00: 00: 000AM and less than 12/26/2000 12: 00: 00: 000AM record. In other words, it will return it correctly 2000
Annual Christmas is entered every record.
Another way is that you can use LIKE to return the correct record. By including wildcard "%" in the date expression,
You can match all time for a specific date. Here is an example:
Select * from Weblog Where Entrydate Like 'DEC 25 2000%
This statement can match the correct record. Because the wildcard "%" represents any time. Use these two functions of these two matches, you can choose to choose a month, one day, one year, for an hour,
A minute, one second, or even in a millisecond record. However, if you use Like to match the second or
Second, you first need to use the function control () to convert the date and time to a more precise format (see front "
Date and time "section).
Compare date and time
Finally, there are two dates and time functions to remove records based on date and time. Use function dateadd
() And Datediff (), you can compare the day before the date. For example, the following SELECT statement will display the table
Each record has been entered for a few hours:
SELECT Entrydate 'Time Entered'
Datediff (hh, entrydate, getdate ()) 'Hours ago' from Weblog
If the current time is from 6:15 on November 30, 2000, it will return the following results:
Time entered Hours Ago
....................................................
Dec 30 2000 4:09 PM 2
DEC 30 2000 4:13 PM 2
DEC 1 2000 4:09 PM 698
(3 row (s) affected)
The parameter of the function Dadediff () is three variables. The first variable specifies some part of the date. In this example,
It is compared to the date at an hour, (To learn more about the details of the date, please refer to Table 11.2) at Date 2000
There is 689 hours from the designated time of November 1 and November 30, 2000. The other two parameters are going on
Comparison time. In order to return a positive number, the earlier time should be given first.
The function dateAdd () adds two dates. This function when you need to calculate the data of the deadline.
It is useful. For example, assume that visitors must first register to use your site. After registration, they can free
Use your site for a month. To determine when their free time will be used, you can use the following SELECT
Statement:
Select Username 'Ser Name',
Dateadd (mm, 1, firstvisit_date) 'registration expires'
From seriousration_table
The parameter of the function dateAdd () has three variables. The first variable represents a certain part of the date (see Table 11.2),
This example uses the MM representing the month. The second variable specifies the interval of time - in this example is one month.
The last variable is a date, in this example, the date is taken from the DateTime field firstvisit_dat e.
Suppose the current date is June 30, 2000, this statement will return the following:
User Name Registration Expires
............................................................................
Bill Gates Jul 30 2000 4:09 PM
President Clinton Jul 30 2000 4:13 PM
William Shakespeare Jul 1 2000 4:09 PM
(3 row (s) affected)
note:
In contrast to your expected, use the function dateadd () plus a date with a month, it does not add 30 days. This function simply adds the month value to 1. This means that people registered in November will get more than 2 days than those registered in February.
Or 3 days. To avoid this problem, you can use the function dateadd () to add the number of days directly, not the month.
Part.
