Potential Values for datepart in Font

Generation PDF 417 in Font Potential Values for datepart

ISOWeek is a numbering system used to give every week in the calendar a unique, ascending number. An ISO week starts on a Monday, and Week 1 is the week containing the first Thursday of that year. For example, in 2008, the first Thursday occurred on January 3, so Week 1 ran from December 31, 2007, through January 6, 2008. Timezone offset Nanosecond Microsecond Millisecond Second Minute Hour Weekday Week Day Day of year Month Quarter Year

Taking the second option of the datepart function, to add the value, make the number positive, and to subtract a number, make it negative. Moving to the final option of the datepart function, this can be either a value, a variable, or a column date type holding the date and time you wish to change.

Try It Out: DATEADD()

1. We will set a local variable to a date and time. After that, we will add four hours to the value and display the results, as shown in Figure 11-20. DECLARE @OldTime datetime SET @OldTime = '24 March 2008 3:00 PM' SELECT DATEADD(hh,4,@OldTime)

Figure 11-20. Adding hours to a date 2. Taking the reverse, we will take the same variable and remove six hours. The results should appear as shown in Figure 11-21. DECLARE @OldTime datetime SET @OldTime = '24 March 2008 3:00 PM' SELECT DATEADD(hh,-6,@OldTime)

DATEDIFF()

To find the difference between two dates, you would use the function DATEDIFF(). The syntax for this function is DATEDIFF(datepart, startdate, enddate) The first option contains the same options as for DATEADD(), and startdate and enddate are the two days you wish to compare. A negative number shows that the enddate is before the startdate.

Try It Out: DATEDIFF()

We will set two local variables to a date and time. After that, we find the difference in milliseconds. DECLARE @FirstTime datetime, @SecondTime datetime SET @FirstTime = '24 March 2008 3:00 PM' SET @SecondTime = '24 March 2008 3:33PM' SELECT DATEDIFF(ms,@FirstTime,@SecondTime)

DATENAME()

Returning the name of the part of the date is great for using with things such as customer statements. Changing the number 6 to the word June makes for more pleasant reading. The syntax is DATENAME(datepart, datetoinspect) We will also see this in action in DATEPART().

Try It Out: DATENAME()

In this example, we will set one date and time and then return the day of the week. We know this to be a Monday. DECLARE @StatementDate datetime SET @StatementDate = '24 March 2008 3:00 PM' SELECT DATENAME(dw,@StatementDate) Figure 11-23 shows the results after executing this code.

DATEPART()

If you wish to achieve returning part of a date from a date variable, column, or value, you can use DATEPART() within a SELECT statement. As you may be expecting by now, the syntax has datepart as the first option, and then the datetoinspect as the second option, which returns the numerical day of the week from the date inspected. DATEPART(datepart, datetoinspect)

Try It Out: DATEPART()

1. We need to set only one local variable to a date and time. After that, we find the day of the month. DECLARE @WhatsTheDay datetime SET @WhatsTheDay = '24 March 2008 3:00 PM' SELECT DATEPART(dd, @WhatsTheDay) Figure 11-24 shows the results after executing this code.

Figure 11-24. Finding part of a date 2. To produce a more pleasing date and time for a statement, we can combine DATEPART() and DATENAME() to have a meaningful output. The function CAST(), which we will look at in detail shortly, is needed here, as it is a data type conversion function. DECLARE @WhatsTheDay datetime SET @WhatsTheDay = '24 March 2008 3:00 PM' SELECT DATENAME(dw, @WhatsTheDay) + ', ' + CAST(DATEPART(dd,@WhatsTheDay) AS varchar(2)) + ' ' + DATENAME(mm,@WhatsTheDay) + ' ' + CAST(DATEPART(yyyy,@WhatsTheDay) AS char(4)) 3. When this is executed, it will produce the more meaningful date shown in Figure 11-25.