SQl Query on form returning value of 1/1/0001

[dougancil]

I am developing an application that based on when a user has last entered a payroll, my program tells them the next available Sunday that is available to them based on

1. The date that the last time payroll was entered
2. If payroll ran = "yes" or "no"

I have a sql query that returns the next Sunday back to me based on this criteria, but when I put it into my application, it returns 1/1/0001 to me.

Here is the query:

select MAX(payrolldate) AS [payrolldate], <br> 
dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday] <br> 
from payroll <br> 
where payrollran = 'no'

and here is my vb code as it stands now, with my old query in it:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br> 
Dim connectionString As String<br> 
Dim cnn As SqlConnection<br> 
Dim myCommand As SqlCommand<br> 
Dim dteReturnValue As DateTime = Nothing<br> 
'the connection string to the SQL server'<br> 
connectionString = "Data Source=xxxxxx;Initial Catalog=MDR;uid=xxxxxxx;password=xxxxxxx"<br> 
cnn = New SqlConnection(connectionString)<br> 
cnn.Open()<br> 
'the SQL query'<br> 
Try<br> 
myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")<br> 
myCommand.Connection = cnn<br> 
ReturnValue = myCommand.ExecuteScalar()<br> 
If ReturnValue IsNot Nothing Then<br> 
dteReturnValue = Convert.ToDateTime(ReturnValue)<br> 
dteReturnValue = dteReturnValue.AddDays(1)<br> 
End If<br> 
Catch exp As SqlException<br> 
End Try<br> 
cnn.Close()<br> 
Dim ButtonDialogResult As DialogResult<br> 
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)<br> 
If ButtonDialogResult = Windows.Forms.DialogResult.OK Then<br> 
Button2.Enabled = True<br> 
Button1.Enabled = False<br> 
End If<br> 
End Sub

I understand that the reason that I'm getting 1/1/0001 back to me is that it's a blank response. Can anyone help me with this?

Thank you

Doug

[lolafuertes]

maybe you can try to change your query to some thing like:

select MAX(payrolldate) AS [payrolldate], 
dateadd(dd, 7 - DATEPART(weekday,MAX(payrolldate)), MAX(payrolldate)) AS [Sunday] 
from payroll where payrollran = 'no'

The hint here is that datepart returns the week day(1 to 7). The return value depends on the value that is set by using SET DATEFIRST. I use 1 (monday) as first day of week so Sunday is 7.

If you use another first day of week, then you must change the query according.

Hope this helps

[dougancil]

Ok so I guess a better question would be if I just use the query, how do I capture the values from SQL to present them back to the user? In other words if I use this query

myCommand = New SqlCommand("select MAX(payrolldate) AS [payrolldate], dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday] from(payroll)where payrollran = 'no' ")
            myCommand.Connection = cnn

I need to pass the value for [Sunday] back to the user here:

ButtonDialogResult = MessageBox.Show(String.Format("The next pay period available to you is"), "Payroll", MessageBoxButtons.OKCancel)