SQL query returning dates of 1/1/0001

[dougancil]

I am building an application where a button click runs a sql query and shows a user the next available date that they can process from. The problem was that it needed to be the following Sunday and not the following day.

That code looked like this:

Public Class Form1
    Dim ReturnValue As Object = Nothing
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        Dim dteReturnValue As DateTime = Nothing
        'the connection string to the SQL server'
        connectionString = "Data Source=xxxxx;Initial Catalog=MDR;uid=xxxxx;password=xxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")
            myCommand.Connection = cnn
            ReturnValue = myCommand.ExecuteScalar()
            If ReturnValue IsNot Nothing Then
                dteReturnValue = Convert.ToDateTime(ReturnValue)
                dteReturnValue = dteReturnValue.AddDays(1)
            End If
        Catch exp As SqlException
        End Try
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show(String.Format("The next pay period available to you is {0} through {1}", dteReturnValue.ToShortDateString(), dteReturnValue.AddDays(7).ToShortDateString), "Payroll", MessageBoxButtons.OKCancel)
        If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
            'pass 'Return Value' to SQL query that will be here'
            Button2.Enabled = True
            Button1.Enabled = False
        End If
    End Sub

But I need to add this query to this application:

select payrolldate,
   Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')
from payroll
where payrollran = 'no'

instead of this query:

"select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")

but when I add that query it just returns a value of 1/1/0001 as the date.

Can anyone assist me with this.

Thank you

Doug

[dougancil]

Anyone?

Can anyone help me with this?