Proper syntax for a sql query

[dougancil]

I have the following code:

 
Imports System.Data.SqlClient
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=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxxx"
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
 
 

and what it will do currently is query a field in my sql database that is a date and return the following date +7 as the Messagebox.show results. I've discovered that this won't do exactly what I need it to do so I have to use this sql query:


select
a.MyDate,
Sunday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+7,'17530107')
from
( -- Test Data
select MyDate = convert(datetime,'20100930')
) a


which should return back the next available sunday to me from the database. What I'm getting back is 01/01/0001 - 01/08/0001.


I've tried a couple of versions of this but they all produce the same date. Can someone please assist.

Thank you


Doug