0 SQL Server 2012 New Date Functions

SQL Server 2008 introduced 4 new data types associated with the date and time, namely, the DATE, TIME, DATETIME2 and DATETIMEOFFSET data types.  The DATE data types stores only a date value while the TIME data type defines a time of a day without time zone awareness and is based on a 24-hour clock.  The DATETIME2 data type defines a date that is combined with a time of day that is based on a 24-hour clock and has a larger date range, a larger default fractional precision and an optional user-specified precision.  Lastly, the DATETIMEOFFSET data type defines a date that is combined with a time of day that has time zone awareness and is based on a 24-hour clock.

SQL Server 2012, on the other hand, is not introducing any new date and time related data types but is introducing a few new date and time functions that return a date and/or time value from their parts.

Function Name	Description	Syntax	Return Data Type
DATEFROMPARTS	Returns a DATE value for the specified year, month and day.	DATEFROMPARTS( <year>, <month>, <day> )	DATE
DATETIME2FROMPARTS	Returns a DATETIME2 value for the specified date and time and with the specified precision.	DATETIME2FROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> )	DATETIME2
DATETIMEFROMPARTS	Returns a DATETIME value for the specified date and time.	DATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )	DATETIME
DATETIMEOFFSETFROMPARTS	Returns a DATETIMEOFFSETvalue for the specified date and time and with the specified offsets and precision.	DATETIMEOFFSETFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision> )	DATETIMEOFFSET
SMALLDATETIMEFROMPARTS	Returns a SMALLDATETIMEvalue for the specified date and time.	SMALLDATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute> )	SMALLDATETIME
TIMEFROMPARTS	Returns a TIME value for the specified time and with the specified precision.	TIMEFROMPARTS( <hour>, <minute>, <seconds>, <fractions>, <precision> )	TIME

Parameters / Arguments :

• <year> - Integer expression specifying a year.
• <month> - Integer expression specifying a month.
• <day> - Integer expression specifying a day.
• <hour> - Integer expression specifying hours.
• <minute> - Integer expression specifying minutes.
• <seconds> - Integer expression specifying seconds.
• <milliseconds> - Integer expression specifying milliseconds.
• <fractions> - Integer expression specifying fractions.  This parameter depends on the <precision> parameter.  For example, if <precision> is 7, then each fraction represents 100 nanoseconds; if <precision> is 3, then each fraction represents a millisecond.
• <hour_offset> - Integer expression specifying the hour portion of the time zone offset.
• <minute_offset> - Integer expression specifying the minute portion of the time zone offset.
• <precision> - Integer literal specifying the precision of the DATETIME2 value to be returned.

In addition to these date/time functions that returns date and time values from their parts, one other new date/time function is the EOMONTH which returns the last day of the month that contains the specified date, with an optional offset.

Function Name	Description	Syntax	Return Data Type
EOMONTH	Returns the last day of the month that contains the specified date, with an optional offset.	EOMONTH( <start_date> [, <month_to_add>] )	Date type of <start_date> or DATETIME2(7)

Parameters / Arguments :

• <start_date> - Date expression specifying the date for which to return the last day of the month.
• <month_to_add> - Optional integer expression specifying the number of months to add to <start_date>.

0 SQL Server DATEDIFF() Function

Definition and Usage

The DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(datepart,startdate,enddate)

Where startdate and enddate are valid date expressions and datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

---

Example

Now we want to get the number of days between two dates.

We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

Result:

DiffDate
61

Example

Now we want to get the number of days between two dates (notice that the second date is "earlier" than the first date, and will result in a negative number).

We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

Result:

DiffDate
-61

Source : w3schools.com

0 How to Sort Alphanumeric Data in SQL

Introduction

If your SQL query is not returning the result-set in the order you are expecting, this article may be helpful to fix the issue.

Background

We all know that the ORDER BY keyword is used to sort a result-set by a specified column. It works great for most of the cases. But, for alphanumeric data, it may not return the result-set that you will be expecting. This article explains how this can be fixed easily.

Using the Code

Step 1

I have created a table named “Test” with two columns, as shown below:

The following data has been added to the “Test” table:

The “Order By” in the following SQL query may not return the result-set in the correct order.

 Collapse | Copy Code

Select ID From TestOrder by ID

Step 2

I have modified the ORDER BY clause as shown below, and it returned the results in the proper order.

(Note: The ID column is defined as varchar(20). So, I did the following to fix this issue:

• If ID is numeric, add 21 '0's in front of the ID value and get the last 20 characters
• If ID is not numeric, add 21 ‘’s at the end of the ID value and get the first 20 characters

 Collapse | Copy Code

Select ID 
From Test
ORDER BY
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
     When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
     Else ID
End

Step 3

I have changed the query to return the row numbers (used in pagination) and it worked!

(Note: ROW_NUMBER works only in SQL Server 2005 and above versions.)

 Collapse | Copy Code

Select Row_Number() Over (Order by
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
                          When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
                        Else ID
               END) As RowNumber,
ID
From Test

Credits : codeproject.com

0 Concatenate row values T-SQL

DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME)
DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT)
DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT  *,
        ( 
                SELECT  u.FName + ','
                FROM    @Users u INNER JOIN     
                                @Reviewers rs ON u.UserID = rs.UserID
                WHERE   rs.ReviewID = r.ReviewID
                FOR XML PATH('')
        ) AS ProductsFROM    @Reviews r