|
|
21. How do I
convert a date stored as decimal or numeric in the YYYYMMDD format into a
datetime or smalldatetime data type?
|
To convert a date stored as decimal or numeric in the YYYYMMDD format into a
datetime or smalldatetime data type, you can use the CAST function.
The CAST function explicitly converts an expression of one data type to
another.
But since the date is stored as a decimal or numeric data type, two calls to the CAST
function needs to be done. The first CAST will convert the decimal
or numeric data type to VARCHAR. The second call of the CAST function
will convert the VARCHAR into DATETIME or SMALLDATETIME data type.
To illustrate, to convert the date 20050704 which is stored as a decimal or
numeric data type into a datetime or smalldatetime data type:
SELECT CAST(CAST(20050704 AS VARCHAR(8)) AS DATETIME)
If the decimal or numeric date is converted straight to datetime without
converting it first to VARCHAR, the following error will be encountered:
SELECT CAST(20050704 AS DATETIME)
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.
|
|
22. How do I
get the first day of the quarter?
|
In SQL Server, there's no built-in function that will return the first day of
the quarter for any given date. Here's a link to a user-defined function
that returns the date of the first day of the quarter for any given input date:
User-Defined Function - Get
First Day of the Quarter
|
|
23. How can I
display a date in DD/MM/YYYY format?
|
To display a date in the DD/MM/YYYY format, you will be using the CONVERT
function. The syntax of the CONVERT function is as follows:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression is any valid Microsoft® SQL Server™ expression. data_type
is the target system-supplied data type. length is an optional
parameter of nchar, nvarchar, char, varchar, binary or varbinary data
types. Lastly, style is the style of date format used to convert
datetime or smalldatetime to character data.
To display the date in the DD/MM/YYYY format, the value to be passed to the style
parameter will be 103, which is the British or French date standard:
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
If today is December 25, 2000, the output of this will be:
DD/MM/YYYY
------------
25/12/2000
It should be noted that the output is now of VARCHAR data type and not DATETIME
data type.
|
|
24. How do I
add or subtract hours to a datetime or smalldatetime value?
|
To add or subtract hours to a datetime or smalldatetime value, you will use
the DATEADD date function. The DATEADD date function
returns a new datetime value based on adding an interval to the specified
date. The syntax of the DATEADD date function is as follows:
DATEADD ( datepart , number, date )
datepart is the parameter that specifies on which part of the date to
return a new value. For hours, you can use either HOUR or HH.
number is the value used to increment datepart. date
is an expression that returns a datetime or smalldatetime value,
or a character string in a date format.
Here's an example on how to use the DATEADD date function to increase or
decrease a datetime value by a certain number of hours:
SELECT DATEADD(HOUR, -12, GETDATE())AS [TwelveHoursAgo]
SELECT DATEADD(HH, 6, GETDATE()) AS [SixHoursLater]
|
|
25. How can I
get the last day of the month?
|
In SQL Server, there's no built-in function that will return the last day of the
month for any given date. Here's a link to a user-defined function that
returns the date of the last day of the month for any given input date:
User-Defined Function - Get Last
Day of the Month
|
|
|
|
26. How can I
get the difference in months between two datetime or smalldatetime values?
|
To get the difference in months between two datetime or smalldatetime values,
you will be using the DATEDIFF date function. The DATEDIFF date
function returns the number of date and time boundaries crossed between two
specified dates and its syntax is as follows:
DATEDIFF ( datepart , startdate , enddate
)
datepart is the parameter that specifies on which part of the date to
calculate the difference. Since we are looking for the difference in
months, we will be passing a value of MONTH, MM, or M
in this parameter. startdate is the beginning date for the
calculation and it's an expression that returns a datetime or smalldatetime
value, or a character string in a date format. enddate is the
ending date for the calculation and it's an expression that returns a datetime
or smalldatetime value, or a character string in a date format.
|
|
27. How do I
get the day of the year for a given date?
|
To get the day of the year for a given date you will make use of the DATEPART
date function. The DATEPART date function returns an integer
representing the specified datepart of the specified date.
The syntax of the DATEPART date function is as follows:
DATEPART ( datepart , date )
The datepart is the parameter that specifies the part of the date to
return. For the day of the year, you will specify either DY or Y.
The date is an expression that returns a datetime or smalldatetime
value, or a character string in a date format. Use the datetime
data type only for dates after January 1, 1753.
Here's an example of how to use the DATEPART date function to extract the
day of the year from the current system date:
SELECT DATEPART(DY, GETDATE()) AS [CurrentDayOfTheYear]
|
|
28. How do I
store a date value before January 1, 1753?
|
Since the minimum date value that a DATETIME data type can have is January 1,
1753, you have to use either INT, DECIMAL/NUMERIC or VARCHAR data type to hold
a date value before January 1, 1753. Of these three data types, the best
data type to use is the INT data type because it will only require 4 bytes to
store the data. DECIMAL or NUMERIC data type will require 5 bytes to
store the date value in YYYYMMDD format while a VARCHAR data type will use 8
bytes.
Regardless of the data type you will use, it is important that the format to use
to store the date value is in the YYYYMMDD format. If the date value is
stored in this format, sorting it by this value will still result in a correct
chronological order.
|
|
29. How do I
get the number of days in a year?
|
In SQL Server, there's no built-in function that will return the number of days
in a year for a given date. Here's a link to a user-defined function that
returns the number of days in a year for the given input date:
User-Defined Function - Get Number of
Days in a Year
|
|
30. Question
30?
|
|
Related Topics:
|
|
|