473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL query returning dates of 1/1/0001

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

✓ answered by dougancil

Can anyone help me with this?

1 1904
dougancil
347 100+
Can anyone help me with this?
Oct 29 '10 #2

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

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
5
by: Irfan | last post by:
Hi All, I am trying to create a report but having problem with the critiera selection logic, please help. I have the following fields date1 date2 date3
3
by: Spook | last post by:
I am tring to write build a query where the outcome of a result depends on one date being older than the other. example Field Name: IIf(=Null,Null,IIf(<,,Null)) However the result comes up as...
3
by: Sim Zacks | last post by:
I am using 8.0 beta 1 on an RH 8 Linux server. I have a union query that I am converting from access (where it worked) and it is returning duplicates. The only difference between the two rows is...
60
by: tdotsmiley | last post by:
Hi all, I need some help with a sql query I am trying to do. I had a sheet which I copied from excel. Currently the columns which I have are Name, Phone, W1, W2, W3, W4, W5, W6, W7. These W...
13
by: Karl Groves | last post by:
I'm missing something very obvious, but it is getting late and I've stared at it too long. TIA for responses I am writing a basic function (listed at the bottom of this post) that returns...
18
by: mlcampeau | last post by:
I have a lengthy query that I am now trying to filter. The query calculates an employee's Anniversary Date in which they are eligible for the next level of Annual Vacation. (i.e. For 1-6 years of...
7
daniel aristidou
by: daniel aristidou | last post by:
Hi im a bit stuck on querying with dates. ive got a databse that querries using sql in visual basic 6. I need to select only the dates which have passed or are today. But the query returns all...
5
by: jennwilson | last post by:
Using Access 2000 - I have a query that is suppose to return the records from table within specified time range and find matching data from another table . Table houses Clinician name, location...
5
natalie99
by: natalie99 | last post by:
Hi everyone I have a small problem, which is making it impossible to acheive my db aims! I would like to know how to write a query that will tell me if a field item is in IN another table,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.