Using DateDiff to calculate days before a Birthday

  • 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

  • Found error.

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

    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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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.

  • 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."

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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."

    The correct way to calculate age is to follow whatever regulations affect your industry or the operational definition of "age" specific to your business goal.

    In the U.S., at least, different agencies calculate leap day baby ages differently.

  • Of course. I should have said "In absence of any industry rules or guidelines, ..."

    However, the Common Law still says that if a debt is due on February 24th of a leap year, it may be paid on 25th (the second, inserted 24th).

  • Jeff Hi,

    No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,

    But I cannot get rid of the error.

  • Jeff Hi,

    No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,

    But I cannot get rid of the error.

  • Sorry, Ken... I was a bit asleep at the switch and forgot this was for the compact edition (heh... "CE" actually stands for "Crippled Edition", doesn't it? :-P)

    There's more than one way to do the same thing so, let's check...

    What do you get in CE when you run each of these individually?

    SELECT YEAR(GETDATE())

    SELECT DATEPART(yy,GETDATE())

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply