SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using DateDiff to calculate days before a Birthday


Using DateDiff to calculate days before a Birthday

Author
Message
kenkob
kenkob
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 64
The following code will only return "No birthday(s) Pending". Any advice on correcting code would be appreciated.


Imports System
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO
Imports System.ComponentModel
Imports System.Windows.Forms

Public Class frmKSBCards
Dim cn As SqlCeConnection
Dim PersonsCommand As SqlCeCommand
Dim PersonsTable As DataTable
Dim PersonsAdapter As SqlCeDataAdapter
Dim Persons As SqlCeDataAdapter
Public strWarnings As StringBuilder
Dim bFoundDatesToRemember As Boolean

Private Sub frmKSBCards_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'connect to the database
Try
cn = New SqlCeConnection("Data Source=|DataDirectory|DAL\Contacts.sdf")
cn.Open()
Catch sqlex As SqlCeException
Dim SqlError As SqlCeError
For Each SqlError In sqlex.Errors
MessageBox.Show(SqlError.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'Select/Retrieve(records)
Dim Sql As String = "SELECT Surname, P.Firstname, P.Dob, " &
"DATEDIFF(Year, P.Dob, GETDATE()) AS AgeNow " &
"FROM Addresses " &
"INNER JOIN Persons P " &
"ON Addresses.AddressID = P.AddressID"
PersonsCommand = New SqlCeCommand(Sql, cn)
PersonsAdapter = New SqlCeDataAdapter()
PersonsAdapter.SelectCommand = PersonsCommand
PersonsTable = New DataTable()
Dim ds As New DataSet
PersonsAdapter.Fill(PersonsTable)
PersonsAdapter.Fill(ds)
dgv.DataSource = ds.Tables(0)
strWarnings = New StringBuilder
CheckRenewals()
End Sub

Private Sub btnRenewals_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRenewals.Click
'Display renewal status if any is available
If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
MessageBox.Show(strWarnings.ToString(), "Current Dates Due")
Else
MessageBox.Show("No pending Birthday(s) due", "No Dates Available")
End If
End Sub

Private Sub CheckRenewals()
'Get Birthday and Anniversary dates
Try
Dim DatesView As DataView = PersonsTable.DefaultView
With DatesView
Dim bFoundDatesToRemember As Boolean = False
Dim sDateReminders As String = String.Empty
For DateCheckCounter As Integer = 0 To .Count - 1
With .Item(DateCheckCounter)

'Get Date from database fields Dob
Dim CardDate1 As Date = CType(.Item("Dob"), Date)
'Days difference between two dates
Dim NumberOfDays1 As Long = DateDiff(DateInterval.Day, Now, CardDate1) + 1

'Test For Dob
If NumberOfDays1 < 10 Then
If NumberOfDays1 > -1 And NumberOfDays1 < 0 Then
Dim DayString As String = IIf(NumberOfDays1 = -1, " day", " days").ToString
ElseIf NumberOfDays1 > -1 Then
bFoundDatesToRemember = True
If sDateReminders <> String.Empty Then sDateReminders &= Environment.NewLine
Dim DayString As String = IIf(NumberOfDays1 = 1, " day", " days").ToString
sDateReminders &= .Item("P.Firstname").ToString.Trim & " Birthday due in " & _
NumberOfDays1.ToString & DayString
End If
End If

If (Not String.IsNullOrEmpty(sDateReminders)) Then
strWarnings.Append(sDateReminders & Environment.NewLine)
End If
End With
Next
End With

Catch ex As Exception
MessageBox.Show(ex.Message, "Errror - Notification")
Exit Sub
Finally
tmrReminders.Enabled = False
If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
With niReminders
.Visible = True
.BalloonTipIcon = ToolTipIcon.Warning
.BalloonTipText = "You have outstanding cards to action"
.BalloonTipTitle = "Cards Due for Action"
.ShowBalloonTip(5)
End With
End If
End Try
End Sub


End Class



kenkob
kenkob
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 64
Found error.
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12779 Visits: 13559
kenkob (9/4/2010)
Found error.


Great!
Now, would you mind posting what therror was so others might benefit?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114364 Visits: 41371
kenkob (8/26/2010)
The following code will only return "No birthday(s) Pending". Any advice on correcting code would be appreciated.


Imports System
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO
Imports System.ComponentModel
Imports System.Windows.Forms

Public Class frmKSBCards
Dim cn As SqlCeConnection
Dim PersonsCommand As SqlCeCommand
Dim PersonsTable As DataTable
Dim PersonsAdapter As SqlCeDataAdapter
Dim Persons As SqlCeDataAdapter
Public strWarnings As StringBuilder
Dim bFoundDatesToRemember As Boolean

Private Sub frmKSBCards_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'connect to the database
Try
cn = New SqlCeConnection("Data Source=|DataDirectory|DAL\Contacts.sdf")
cn.Open()
Catch sqlex As SqlCeException
Dim SqlError As SqlCeError
For Each SqlError In sqlex.Errors
MessageBox.Show(SqlError.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'Select/Retrieve(records)
Dim Sql As String = "SELECT Surname, P.Firstname, P.Dob, " &
"DATEDIFF(Year, P.Dob, GETDATE()) AS AgeNow " &
"FROM Addresses " &
"INNER JOIN Persons P " &
"ON Addresses.AddressID = P.AddressID"
PersonsCommand = New SqlCeCommand(Sql, cn)
PersonsAdapter = New SqlCeDataAdapter()
PersonsAdapter.SelectCommand = PersonsCommand
PersonsTable = New DataTable()
Dim ds As New DataSet
PersonsAdapter.Fill(PersonsTable)
PersonsAdapter.Fill(ds)
dgv.DataSource = ds.Tables(0)
strWarnings = New StringBuilder
CheckRenewals()
End Sub

Private Sub btnRenewals_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRenewals.Click
'Display renewal status if any is available
If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
MessageBox.Show(strWarnings.ToString(), "Current Dates Due")
Else
MessageBox.Show("No pending Birthday(s) due", "No Dates Available")
End If
End Sub

Private Sub CheckRenewals()
'Get Birthday and Anniversary dates
Try
Dim DatesView As DataView = PersonsTable.DefaultView
With DatesView
Dim bFoundDatesToRemember As Boolean = False
Dim sDateReminders As String = String.Empty
For DateCheckCounter As Integer = 0 To .Count - 1
With .Item(DateCheckCounter)

'Get Date from database fields Dob
Dim CardDate1 As Date = CType(.Item("Dob"), Date)
'Days difference between two dates
Dim NumberOfDays1 As Long = DateDiff(DateInterval.Day, Now, CardDate1) + 1

'Test For Dob
If NumberOfDays1 < 10 Then
If NumberOfDays1 > -1 And NumberOfDays1 < 0 Then
Dim DayString As String = IIf(NumberOfDays1 = -1, " day", " days").ToString
ElseIf NumberOfDays1 > -1 Then
bFoundDatesToRemember = True
If sDateReminders <> String.Empty Then sDateReminders &= Environment.NewLine
Dim DayString As String = IIf(NumberOfDays1 = 1, " day", " days").ToString
sDateReminders &= .Item("P.Firstname").ToString.Trim & " Birthday due in " & _
NumberOfDays1.ToString & DayString
End If
End If

If (Not String.IsNullOrEmpty(sDateReminders)) Then
strWarnings.Append(sDateReminders & Environment.NewLine)
End If
End With
Next
End With

Catch ex As Exception
MessageBox.Show(ex.Message, "Errror - Notification")
Exit Sub
Finally
tmrReminders.Enabled = False
If (Not String.IsNullOrEmpty(strWarnings.ToString())) Then
With niReminders
.Visible = True
.BalloonTipIcon = ToolTipIcon.Warning
.BalloonTipText = "You have outstanding cards to action"
.BalloonTipTitle = "Cards Due for Action"
.ShowBalloonTip(5)
End With
End If
End Try
End Sub


End Class




Whether you fixed the code or not, the biggest problem is... embedded SQL.

The second biggest problem is... the embedded SQL calculation is totally wrong for the proper calculation of age in years.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kenkob
kenkob
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 64
The following corrected code is used just to calculate the days to the next birthday only from dates within a SqlCe database, it is not being used to calculate the age in years. As I am using Sql Compact Edition I do not know of any other method to incorporate Sql except in code. I would therefore be pleased to see your updated version of my code without embedded Sql and exactly where the Sql would be called from. The code below now does what I expected it to do. It may not be the best way, certainly it is not the only way, it is my way and part of my learning curve.

[code = "vb"]
Imports System
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO
Imports System.ComponentModel
Imports System.Windows.Forms

Public Class frmKSBCards
Dim cn As SqlCeConnection
Dim PersonsCommand As SqlCeCommand
Dim PersonsTable As DataTable
Dim PersonsAdapter As SqlCeDataAdapter
Public strWarnings As StringBuilder

Private Sub frmKSBCards_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'connect to the database
Try
cn = New SqlCeConnection("Data Source=|DataDirectory|DAL\Contacts.sdf")
cn.Open()
Catch sqlex As SqlCeException
Dim SqlError As SqlCeError
For Each SqlError In sqlex.Errors
MessageBox.Show(SqlError.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'Select/Retrieve(records)
Dim Sql As String = "SELECT * FROM Persons ORDER BY Dob" 'Surname, P.Firstname, P.Dob, " &
PersonsCommand = New SqlCeCommand(Sql, cn)
PersonsAdapter = New SqlCeDataAdapter()
PersonsAdapter.SelectCommand = PersonsCommand
PersonsTable = New DataTable()
Dim ds As New DataSet
PersonsAdapter.Fill(ds)
dgv.DataSource = ds.Tables(0)
strWarnings = New StringBuilder
With tmrReminders
.Interval = 1000 * 60 * 20
End With
CheckRenewals()
End Sub

Private Sub CheckRenewals()
With tmrReminders
.Enabled = False
PersonsAdapter.Fill(PersonsTable)
'Get Birthday and Anniversary dates
Dim PersonDatesView As DataView = PersonsTable.DefaultView
With PersonDatesView
If .Count > 0 Then
Dim bFoundBirthdaysToRemember As Boolean = False
Dim sBirthdayReminders As String = vbNullString
For BirthdayCheckCounter As Integer = 0 To .Count - 1
With .Item(BirthdayCheckCounter)
Dim PersonBirthday As Date = CType(.Item("Dob"), Date)
Dim PersonBirthDate As Date = CType(PersonBirthday.Day & "/" & PersonBirthday.Month & "/" & Now.Year, Date)
Dim NoOfDays As Long = DateDiff(DateInterval.Day, Now, PersonBirthDate) + 1
'Test For Dob
If NoOfDays < 10 Then
If NoOfDays > -10 And NoOfDays < 0 Then
ElseIf NoOfDays > -1 Then
bFoundBirthdaysToRemember = True
If sBirthdayReminders <> vbNullString Then _
sBirthdayReminders &= vbCrLf
Dim DayString As String = IIf(NoOfDays = 1, " day", " days").ToString
sBirthdayReminders &= .Item("Firstname").ToString.Trim & " " & _
"'s Birthday due in : " & _
NoOfDays.ToString & DayString
End If
End If
End With
Next
If (bFoundBirthdaysToRemember) Then
With niReminders
.Visible = True
If bFoundBirthdaysToRemember Then
.BalloonTipIcon = ToolTipIcon.Info
.BalloonTipText = sBirthdayReminders
.BalloonTipTitle = "Cards Due for Action"
End If
.ShowBalloonTip(15)
End With
End If
End If
End With
.Enabled = True
End With
End Sub

End Class
[/code]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114364 Visits: 41371
kenkob (9/5/2010)
It may not be the best way, certainly it is not the only way, it is my way and part of my learning curve.


I agree and no personal attack was intended. I intend to add to your "learning curve". :-)

I'm not sure of everything that your code does but I do know you don't need embedded SQL to return a list of persons who's birthday comes up sometime in the next 10 days including today (you can easily change that in the BETWEEN in the code that follows).

The following code can easily be turned into a View, Stored Procedure, or Inline Table Valued Function for use by GUI code or other SQL objects... this particular example goes against the AdventureWorks database included with SQL Server 2005.

 SELECT *
FROM AdventureWorks.HumanResources.Employee
WHERE DATEDIFF(dd,
DATEADD(yy, -(YEAR(GETDATE())-1900),GETDATE()), --Today
DATEADD(yy, -(YEAR(BirthDate)-1900),BirthDate)
) BETWEEN 1 AND 10



That will cause a table scan just like your code did. Someone else may be able to turn it into a "sargeable" statement but I just wanted you to see how simple things can be.

Each DATEADD in the code strips out the year element so the date occurs in 1900 which puts both dates on equal footing. Then, the DATEDIFF simply takes the number of days between the two and it's done.

Your GUI code would only need to open the result set, then. Of course, you'd need to add an ORDER BY for your code and I didn't here because I didn't want to confuse the matter with a JOIN. I just wanted you to concentrate on the date stuff.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kenkob
kenkob
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 64
Jeff,
Thank you for your response, I was not offended by your comments, maybe I expressed my response badly. Thank you for the code sample.

My code was attempting to calculate and inform the user via a NotifyIcon when any Birthday would be due within 10 days. The display of other data was part of my learning curve.

I will now endeavor to rewrite my code using the sample you have shown without displaying unnecessary information.

By the way, what is a 'sargeable' statement.
Nadrek
Nadrek
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2405 Visits: 2729
For extra credit, first define your Leap Day Baby rule, and then implement it correctly. For instance, the rule I most commonly use is Feb 29th birthdays fall on Feb 29th in years where it exists, and on Feb 28th otherwise. Another rule that's just as valid is use Feb 29th in years where it exists, and Mar 1st otherwise.

For credit, first implement it, and then figure out what Leap Day Baby rule you happened to implement, and document that.
Revenant
Revenant
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8578 Visits: 4907
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."
LutzM
LutzM
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12779 Visits: 13559
Revenant (9/7/2010)
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."


Just out of curiousity: any source available for that? (Never heard about it before...)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search