Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using DateDiff to calculate days before a Birthday Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, 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


Post #975671
Posted Saturday, September 4, 2010 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Found error.
Post #980611
Posted Saturday, September 4, 2010 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:33 PM
Points: 7,042, Visits: 12,970
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
Post #980626
Posted Saturday, September 4, 2010 9:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980730
Posted Sunday, September 5, 2010 4:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, 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]
Post #980772
Posted Monday, September 6, 2010 1:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #981235
Posted Tuesday, September 7, 2010 3:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, 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.
Post #981422
Posted Tuesday, September 7, 2010 12:27 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:10 AM
Points: 861, Visits: 2,360
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.
Post #981809
Posted Tuesday, September 7, 2010 12:47 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 4,394, Visits: 3,401
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."
Post #981819
Posted Tuesday, September 7, 2010 12:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:33 PM
Points: 7,042, Visits: 12,970
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
Post #981822
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse