By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,044 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,044 IT Pros & Developers. It's quick & easy.

Proper syntax for a sql query

100+
P: 347
I have the following code:


Expand|Select|Wrap|Line Numbers
  1.  
  2. Imports System.Data.SqlClient
  3. Public Class Form1
  4. Dim ReturnValue As Object = Nothing
  5. Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  6. Dim connectionString As String
  7. Dim cnn As SqlConnection
  8. Dim myCommand As SqlCommand
  9. Dim dteReturnValue As DateTime = Nothing
  10. 'the connection string to the SQL server'
  11.   connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxxx"
  12. cnn = New SqlConnection(connectionString)
  13. cnn.Open()
  14. 'the SQL query'
  15. Try
  16. myCommand = New SqlCommand("select payrolldate from payroll where(payrolldate <= getdate())and payrollran <> 'yes'")
  17. myCommand.Connection = cnn
  18. ReturnValue = myCommand.ExecuteScalar()
  19. If ReturnValue IsNot Nothing Then
  20. dteReturnValue = Convert.ToDateTime(ReturnValue)
  21. dteReturnValue = dteReturnValue.AddDays(1)
  22. End If
  23. Catch exp As SqlException
  24. End Try
  25. cnn.Close()
  26. Dim ButtonDialogResult As DialogResult
  27. 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)
  28. If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
  29. 'pass 'Return Value' to SQL query that will be here'
  30. Button2.Enabled = True
  31. Button1.Enabled = False
  32. End If
  33. End Sub
  34.  
  35.  

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
Oct 1 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.