Get date and time parts in SQL Server 2005 using Transact SQL

Datetime data type in SQL Server 2000 or SQL Server 2005 provides a way to store data in date and time format. This type of variable or field contains both date and time togather, but sometimes we need either date or time portion only separately. For that purpose we can use Transact SQL. In this article I'll explain the way we can process datetime type data and get date and time parts as string.

We'll use some variations of Convert function to get both date and time. Let say we have an AddressBook table in our database which contains an UpdateDateTime field.

First of all we'll get only the time part of this datetime field:

SELECT CONVERT(CHAR(8),UpdateDateTime,8) FROM AddressBook

Above statement will give you the time part in string or text format like 15:13:03, Sowe can use it accordingly.

Following four Transact SQL statements return date part in different formats using Convert funciton:


--Date Part 10-26-2007
SELECT CONVERT(CHAR(10),UpdateDateTime,110) FROM AddressBook
--Date Part 2007/10/26
SELECT CONVERT(CHAR(10),UpdateDateTime,111) FROM AddressBook
--Date Part 20071026
SELECT CONVERT(CHAR(10),UpdateDateTime,112) FROM AddressBook
--Date Part 26 Oct 2007
SELECT CONVERT(CHAR(11),UpdateDateTime,113) FROM AddressBook

The date format returned is given as comments above the statements.

Custom paging in SQL Server stored procedure

Microsoft.net provides controls which have paging capability by default – that is they have built in feature that can let you apply data paging by just setting a few properties or with just a few lines of code. The problem with this approach is that all the data is cashed in the memory i.e. using dataset etc, which effects application performance. Custom data paging in SQL Server stored procedure is a solution to this problem. I’ll explain how to perform this in such a way that you can use it either in SQL Server 2000 or SQL Server 2005 or even later versions.

You need to pass three parameters to the stored procedure which are page number, page size, and sort order. Following stored procedure code will return a page at a particular page number with your specified page size, sorted in your specified order i.e. Asc or Desc.

First of all, pass following three parameter to the stored procedure:

@PageNumber Int,
@PageSize Int,
@SortOrder Varchar(10) = 'DESC'

We have to apply this custom data paging on Article table. First create a temporary table in stored procedure.

Declare @TempTable Table
(
TempID int Identity,
ArticleID Int
)


Following code snippet will help you sort the data in your required order and keep it in temp table.

if @SortOrder='DESC'
Begin
Insert Into @TempTable (ArticleID)
Select ArticleID
From Article
Where StatusID=1
Order By
ArticleID Desc
End
Else
Begin
Insert Into @TempTable (ArticleID)
Select ArticleID
From Article
Where StatusID=1
Order By ArticleID Asc
End

The reason to keep the data in temporary table first is just to attach a unique id in a sequence to help get a proper page from the table. As, in your actually table some records might have been removed, so if you would apply paging directly on Article table, it might not return proper set or records for a particular page.

Following two local variables will help you get the proper page.

Declare @StartIndex Int
Declare @EndIndex Int

You can perform custom paging using either 1 based or 0 based index. The formulas for both approaches are as under. You can use any one of your choice.


--If page number is 1 based
Set @StartIndex = ((@PageNumber-1)* @PageSize) +1
Set @EndIndex = @PageNumber * @PageSize

--if page number is 0 bases then
Set @StartIndex = (@PageNumber * @PageSize) + 1
Set @EndIndex = @PageSize * (@PageNumber + 1)

Now, just perform a join of Article and TempTable and get all the records between StartIndex, and EndIndex.

Select E.* From Article E
Inner Join @TempTable T
On E.ArticleID= T.ArticleID
Where T.TempID Between @StartIndex And @EndIndex