Home > DeveloperSection > Forums > The orders after 12 am to 7 am not show timer problem
ahmed salah
ahmed salah

Total Post:12

Posted on    March-31-2015 7:39 AM

 C# C# 

 4 Reply(s)
 916  View(s)
Rate this:
Hi guys

I make program to select data every day automatically by timer every second in c# 

In sql server 2005 i write the following query 

ALTER proc [dbo].[JeddahSalesAll]
SELECT     ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [S.N], dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'OrderNo', 
dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'CustomerNo', dbo.[Jeddah-Live$Sales Line].Area AS 'Shippment Type', dbo.[Jeddah-Live$Sales Line].Description AS 'Description', 
dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'PumpNo', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS int) AS 'Required Qunatity', 
CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS int) AS 'Shipped Quantity', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0, 
1) AS int) AS 'Outstanding Qunatity '
FROM         dbo.[Jeddah-Live$Sales Header] INNER JOIN
dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND 
dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]  
WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0


ales.SalesClass SalesClass1 = new Sales.SalesClass();
DataTable dt = SalesClass1.ShowSalesData("Data Source=;Initial Catalog=Altawi-last06-01-2015;User ID=admin;Password=123");
dataGridView1.DataSource = dt;
dataGridView1[0, dataGridView1.Rows.Count - 1].Value = "Total Sum";
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Style.BackColor = Color.Yellow;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[1].Style.ForeColor = Color.Red;


class SalesClass
public DataTable ShowSalesData(string ConnectionString)
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "JeddahSalesAll";
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
DataTable dt = ds.Tables[0];
return dt;

code above work success no any problem

only problem the order start 8 pm in date(31/03/2015) and finish 7 am in date(01/04/2015)

at next day

what i do to show the orders done from 12 am to 7 am to show

if you have solution or suggestion to solve problem help me if possible


Mayank Tripathi
Mayank Tripathi

Total Post:397

Posted on    March-31-2015 8:41 AM

Insted of DATEDIFF use BETWEEN. 

Your DATEDIFF condition will not work in your scenario as date is changed after 12AM and Date difference now become 1 and you are checking it with 0.

Better approach for you to use BETWEEN(dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) in your where clause.

ahmed salah
ahmed salah

Total Post:12

Posted on    April-01-2015 11:05 AM

to more show what i need

shipped date(user enter it)     time         dateof insert(server date)

31/03/2015                              8  pm        31/03/2015

31/03/2015                             10 pm        31/03/2015

31/03/2015                              1 am          01/04/2015

31/03/2015                              7 am          01/04/2015

query above return 8 pm and 10 pm

but not show 1 am and 7 am

so taht what i need to show is 8 pm,10 pm,1 am,7 am

only I need to show orderes that happen in same day and data shipped after 12 until 7 am

Meaning if order still work two days i need to show it based on date starting in it

we put 31/03/2015 in the day of 01/04/2015 because shipping date start in 31/03/2015

but problem happen when order still work to another day

Mayank Tripathi
Mayank Tripathi

Total Post:397

Posted on    April-02-2015 9:23 AM

hi ahmed,

Just query on Shipped date(user enter it), it will work.

ahmed salah
ahmed salah

Total Post:12

Posted on    April-02-2015 12:54 PM

 thank you for reply
The problem i put timer in c# code to return shipment date based on date today
and after 12 am midnight day changed and order have date before not show
suppose day work in 31/03/2015 it still until order finish and in this case shipment date will be 31/03/2015 until
order finish and will be finish in 01/04/2015
My question how to show 31/03/2015 when get date 01/04/2015 because when date changed based on getdate
ship date not changed because it register based on date that work start
so that work start will be 31/03/2015 and when date changed to 01/04/2015
no data show and records of 31/03/2015 not show

Don't want to miss updates? Please click the below button!

Follow MindStick