The return value of the function getdate () is displayed only in seconds. In fact, the internal time of SQL Sever can be accurate to milliseconds (exactly, it can be accurate to 3.33 ms).
To get the date and time of different formats, you need to use the function control (). For example, when the statement below is 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. Table 11.1 shows all formats. Table 11.1 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: SS9-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. The moon returned by type 0, 7, and 13 is represented by three characters (represents November by NOV).
For each format listed in Table 11.1, you can add a type value to 100 to display an angelic year (for example, 2000). For example, you should use the date of the Japanese standard, including the century, you should use the following statement: SELECT Convert (VARCHAR (30), getDate (), 111) In this example, the function control converts the date format, 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. For example, suppose you want to list the months that each site is queried in your site directory. At this time, you don't want the full date and time to mess up the web. 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 variable is 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) an affected) Month Posted column shows the month of each site queries. 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 Table 11.2. Table 11.2 Date of the Section of the Date and Its Shredded Date Some Story of the Date of 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 functions when you need to perform date and time comparison DATEPART () returns an integer is useful. However, the query results (2, 5) in the above example are not very easy to read. To get partial dates and time in 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'
The from site_directory function datename () and function datepart () receive the same parameters. However, its return value is a 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) an affected) You can also use the function datenae () to draw a day in a week. The following example simultaneously extracts one day and date in a 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, it will return the following result: 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 interested in a certain day - for example, December 25, 2000 - visitor is interested in your site. To take out this type of data, you may try to use this SELECT statement:
Select * from Weblog where entrydate = "12/25/2000" Don't do this. This SELECT statement does not return the correct record - it will return only the date and time of 12/25/2000 12: 00: 00: 000AM. In other words, only records just entered at midnight zero are returned. Note: In this section, assuming field entrydate is a DateTime type, not a SmallDateTime. The discussion of this section is also applicable to the SmallDateTime field, but the SmallDateTime field can only 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 not entering time, 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 "Jan 1 1900". To return to the correct record, you need to apply the date and time range. There is more than one way to do this. For example, the following SELECT statement will be able to return to 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, which is greater than or equal to 12/25/2000 12: 00: 000AM and less than 12/26/2000 12: 00: 00: 000AM record. In other words, it will correctly return to each record entered by Christmas 2000 Christmas. Another way is that you can use LIKE to return the correct record. By including wildcard "%" in the date expression, you can match all times of 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.
With these two functions of these two matches, you can choose a month, one day, one year, an hour, a minute, one second, or even entered within a millisecond. However, if you use Like to match the second or milliseconds, you first need to use the function convert () to convert the date and time to a more accurate format (see the "Conversion Date and Time" section in front).
