Fungsi Menghitung Umur Di Sql Server

Ini adalah fungsi untuk mengkalkulasi umur seseorang berapa... tinggal dipakai saja sich... daripada kita melakukan kalkulasi umur seseorang di dalam Crystal Report dengan susah payah... lebih baik kita lakukan lewat sql saja... :D

create function dbo.F_AGE_YYYY_MM_DD
(
@START_DATE datetime,
@END_DATE datetime
)
returns varchar(10)
as
begin
declare @AGE varchar(10)
declare @AGE_IN_YEARS int
declare @AGE_IN_MONTHS int
declare @AGE_IN_DAYS int
– Return null if @START_DATE > @END_DATE
if @START_DATE > @END_DATE begin return @AGE end
select
@AGE_IN_YEARS = AGE_IN_YEARS,
@AGE_IN_MONTHS = AGE_IN_MONTHS,
@AGE_IN_DAYS =
datediff(dd,
dateadd(mm,AGE_IN_MONTHS,
dateadd(yy,AGE_IN_YEARS,START_DATE))
,END_DATE)
from
(
select
AGE_IN_MONTHS =
case
when AnniversaryThisMonth <= END_DATE
then datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
else datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)-1
end,
*
from
(
select
AGE_IN_YEARS =
case
when AnniversaryThisYear <= END_DATE
then datediff(yy,START_DATE,END_DATE)
else datediff(yy,START_DATE,END_DATE)-1
end,
*
from
(
select
AnniversaryThisYear =
dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
AnniversaryThisMonth =
dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE),
*
from
(
select START_DATE = dateadd(dd,datediff(dd,0,@START_DATE),0),
END_DATE = dateadd(dd,datediff(dd,0,@END_DATE),0)
) aaaa
) aaa
) aa
) a
select @AGE =
right(’0000′+convert(varchar(4),@AGE_IN_YEARS),4) + ‘ ‘ +
right(’00′+convert(varchar(4),@AGE_IN_MONTHS),2) + ‘ ‘ +
right(’00′+convert(varchar(4),@AGE_IN_DAYS),2)
return @AGE
end
GO
================================
Cara pakenya gini Select dbo.F_AGE_YYYY_MM_DD(Tgl_Lahir, getdate())  From T_Data_Karyawan

Paging Data In DataGridView VB.Net

hai..., sedikit sharing tentang cara melakukan paging didatagridview vb.net..., kebetulan lagi browsing2 ketemu link bagus... hehehe... :D

Fungsi cek jumlah data yang di select

Public Shared Function CekRowSelect(ByVal Sql As String) As Integer
Dim NewSql As String
NewSql = “Select count (*) from (” & Sql & “) a”
Dim sDa As New SqlDataAdapter(NewSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)
Return CType(Dt.Rows(0).Item(0), Integer)
Dt.Dispose()
sDa.Dispose()
End Function

Fungsi untuk ambil data

Public Shared Function GetTableDataPaging(ByVal Sql As String, _
ByVal JmlPerPage As Integer, ByVal JmlSelect As Integer, ByVal OrderBy As String) As DataTable
Dim sSql As String
Dim i As String
Dim a() As String
Dim j As Integer
Dim OrderBySql1 As String = “”
Dim OrderBySql2 As String = “”
i = OrderBy
a = i.Split(“#”)
For j = 0 To a.GetUpperBound(0)
If OrderBySql1 = “” Then
OrderBySql1 = OrderBySql1 & a(j) & ” ASC “
Else
OrderBySql1 = OrderBySql1 & “,” & a(j) & ” ASC “
End If
If OrderBySql2 = “” Then
OrderBySql2 = OrderBySql2 & a(j) & ” DESC “
Else
OrderBySql2 = OrderBySql2 & “,” & a(j) & ” DESC “
End If
Next
sSql = “SELECT * FROM (SELECT TOP ” & JmlPerPage & ” * FROM (SELECT TOP ” & JmlSelect & ” * FROM ( ” & Sql & “) c ORDER BY ” & OrderBySql1 & “) a ” _
& “ORDER BY ” & OrderBySql2 & “) b ORDER BY ” & OrderBySql1
Dim sDa As New SqlDataAdapter(sSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)
Return Dt
Dt.Dispose()
sDa.Dispose()
End Function

Cara pake:

deklarasi di paling atas biar bs diakses dr seluruh proses dl class
Dim StatusPage, JmlHalaman, TotalRecord, JmlRecordPerHal, S1, S2 As Integer
Dim SqlData, SortBy As String
Private Sub InisialisasiData(ByVal Sql As String)
Try
JmlRecordPerHal = 30
TotalRecord = CekRowSelect(Sql)
If TotalRecord Mod JmlRecordPerHal > 0 Then
JmlHalaman = (TotalRecord / JmlRecordPerHal) + 1
Else
JmlHalaman = (TotalRecord / JmlRecordPerHal)
End If
S1 = JmlRecordPerHal
S2 = StatusPage * CInt(S1)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub EksekusiData(ByVal sql As String, ByVal TSort As String)
Try
dt = GetTableDataPaging(sql, S1, S2, TSort)
DGV.Rows.Clear()
For Each Dr As DataRow In dt.Rows
DGV.Rows.Add(SetString(Dr(“ID_Barang”)), SetString(Dr(“Nama_Barang”)), SetString(Dr(“Satuan”)), SetString(Dr(“Kelompok”)), SetString(Dr(“Panjang”)), _
SetString(Dr(“Lebar”)), SetString(Dr(“Tinggi”)), SetString(Dr(“Barcode_Logistik”)), SetString(Dr(“Barcode_ISBN”)), SetString(Dr(“Maksimum_Tumpukan”)), SetString(Dr(“ROQ”)), SetString(Dr(“Prioritas”)))
Next
HeaderColor(DGV)
Blereng2(DGV)
Posisi.Text = “Halaman : ” & StatusPage & ” dari ” & JmlHalaman & “.” & vbNewLine _
& “Halaman ini berisi ” & dt.Rows.Count & ” record.” & vbNewLine & “Seluruh data ” & TotalRecord & ” Record.”
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Load data:

Try
SqlData = “Select * from T_Barang”
SortBy = “Nama_Barang”
StatusPage = 1
InisialisasiData(SqlData)
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try

Navigasi:

Private Sub BtAfter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtAfter.Click
Try
‘MsgBox(StatusPage)
If StatusPage = JmlHalaman – 1 Then
If TotalRecord Mod JmlRecordPerHal > 0 Then
S1 = TotalRecord Mod JmlRecordPerHal
Else
S1 = JmlRecordPerHal
End If
Else
S1 = JmlRecordPerHal
End If
StatusPage = StatusPage + 1
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtBefore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtBefore.Click
Try
StatusPage = StatusPage – 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
Try
StatusPage = 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click

Try

StatusPage = JmlHalaman

If TotalRecord Mod JmlRecordPerHal > 0 Then

S1 = TotalRecord Mod JmlRecordPerHal

Else

S1 = JmlRecordPerHal

End If

S2 = TotalRecord

EksekusiData(SqlData, SortBy)

Catch ex As Exception

Pesan(Err)

End Try

End Sub

Lain waktu saia akan coba untuk mensharekan paging data di datagridview dengan menggunakan storeprocedure di sql server untuk mendapatkan kinerja yang lebih optimal..., hehehe... :)

Referensi :  http://myhandbook.wordpress.com/2008/09/13/paging-data-vbnet-datagridview-dengan-fungsi/