[RESOLVED] How to query data of column Current Date and all Previous Date in sql server 2008

[BONITO]

hi all,

Please help me with my project I am newbie.

I want to query all the data that has the current date and previous date only.

here is my table structure:

Create table PM_Schedule(
Feeder_Name nvarchar(50)primary key not null,
Start_Working_Date Date not null,
Working_Time_Start varchar(10) not null,
Working_Time_Stop varchar(10) not null,
Status nvarchar(10) not null,
Description nvarchar(50),
DateClose Date
)

below is the actual data in my table , as you noticed in column Start_Working_Date I have different date. the highlighted date are the date i want to query only in my other data grid view. they are the current date and the previous date.


how can i do it ?



Note: i don't want to query the date that is greater than the current date.
thank you in advanced.

[jmcilhinney]

Firstly what does:

the current date and previous date

actually mean? Do you mean today's date and yesterday's date?

Secondly, what's up with this:

Create table PM_Schedule(
Feeder_Name nvarchar(50)primary key not null,
Start_Working_Date Date not null,
Working_Time_Start varchar(10) not null,
Working_Time_Stop varchar(10) not null,
Status nvarchar(10) not null,
Description nvarchar(50),
DateClose Date
)

Why would you store time data in a text column? SQL Server has at least one data type (maybe more, can't recall off the top of my head) for time so why would you not use that?

[BONITO]

Today's date and yesterday date will based on the Start_Working_Date Column. that column where I put the schedule date.
so it may composed of current date, previous date and the coming schedule date.
now my question is how to query only the data that has the current date and previous date only ??

about this code below, it doesn't matter I just input there the start time and end time like 08:00 -09:00.

Create table PM_Schedule(
Feeder_Name nvarchar(50)primary key not null,
Start_Working_Date Date not null,
Working_Time_Start varchar(10) not null,
Working_Time_Stop varchar(10) not null,
Status nvarchar(10) not null,
Description nvarchar(50),
DateClose Date
)

[jmcilhinney]

now my question is how to query only the data that has the current date and previous date only ??

I can read what your question is. I already read it. I asked for clarification and, instead of providing that, you just repeated what I had already read. How about you answer the question that I asked so that I can answer yours? Does "current date and previous date" mean today's date and yesterday's date or does it mean something else and, if the latter, what does it actually mean?

it doesn't matter

Of course it matters. If a data type exists that is specific to the type of data you need to store, why would you not use it? What reason do you have to use varchar other than laziness?

[BONITO]

Yes Sir , it means Today's date and Yesterday's date.

I have searched a code for getting the Today's date as below. but can't find a code to include the yesterday's date or the past date.

----get current date
select Feeder_Name,Start_Working_Date,Working_Time_Start,Working_Time_Stop,Status,Description,dateadd(Month,3,Start_Working_Date)as Next_Schedule
from PM_Schedule 
where CONVERT(varchar(10),Start_Working_Date,104)=CONVERT(varchar(20),GetDate(),104)
ORDER BY Start_Working_Date ASC

[jmcilhinney]

Stop using varchar everywhere. SQL Server has data types for dates and times so use them when working with dates and times. Text data types like varchar are for text so use them ONLY for data that actually is text. If you want to learn how to work with dates and times in SQL Server then this would be a good place to start:

https://docs.microsoft.com/en-us/sql...ql-server-2014

That particular documentation only goes back as far as SQL Server 2014 but most of it will be the same anyway and there would also be similar documentation for SQL Server 2008 if you care to look for it, plus similar information available from many other sources. I simply searched for "sql server date time functions" and that was the second match.

[FunkyDexter]

Stop using varchar everywhere. SQL Server has data types for dates and times so use them when working with dates and times.

You may not realise it yet but this piece of advice is HUGELY important. Selecting the correct datatypes for your columns is a big part of database design and it's important to get it right. Want an example of why? Let's look at how you'd calculate the duration of a job in minutes.

Here's the calculation if you used Time fields:-

DateDiff(Minute, Working_Time_Start, Working_Time_Stop)

Here's the calculation if you've used varchars and don't want to use time types at all (so no casting to times)

(Cast(SubString(Working_Time_Stop, 1, 2) as Int) - Cast(SubString(Working_Time_Start, 1, 2) as Int)) * 60 + (Cast(SubString(Working_Time_Stop, 4, 2) as Int) - Cast(SubString(Working_Time_Start, 4, 2) as Int))

...and that's assuming that you will always store the time in an absolute format, so no storing 09:15 as 9:15.

Which looks easier to work with? and we haven't even started to consider performance yet.

Use the correct data types. It matters.


Anyway, on to your actual question. Fortunately you have used the perfect datatype to store the Start Date: a Date. That means you've got a bunch of functions (all of which can be found at the end of JM's link) that will make it really easy to do this sort of calculation. I'd probably go with:-

Where Start_Working_Date Between Cast(DateDiff(Day, -1, GetDate()) as Date) And Cast(GetDate() as Date)

GetDate() returns a DateTime which isn't really what you want as you're concerned with whole days so we cast it to a date to remove the time part. It's then simply a matter of making sure Start_Working_Date falls between the first date your interested in (DateDiff(Day, -1, GetDate()) will give you "yesterday") and the last date you're interested in (GetDate() gives you "today")

[techgnome]

You may not realise it yet but this piece of advice is HUGELY important. Selecting the correct datatypes for your columns is a big part of database design and it's important to get it right. Want an example of why? Let's look at how you'd calculate the duration of a job in minutes.

Here's the calculation if you used Time fields:-

DateDiff(Minute, Working_Time_Start, Working_Time_Stop)

Here's the calculation if you've used varchars and don't want to use time types at all (so no casting to times)

(Cast(SubString(Working_Time_Stop, 1, 2) as Int) - Cast(SubString(Working_Time_Start, 1, 2) as Int)) * 60 + (Cast(SubString(Working_Time_Stop, 4, 2) as Int) - Cast(SubString(Working_Time_Start, 4, 2) as Int))

...and that's assuming that you will always store the time in an absolute format, so no storing 09:15 as 9:15.

Which looks easier to work with? and we haven't even started to consider performance yet.

Use the correct data types. It matters.


Anyway, on to your actual question. Fortunately you have used the perfect datatype to store the Start Date: a Date. That means you've got a bunch of functions (all of which can be found at the end of JM's link) that will make it really easy to do this sort of calculation. I'd probably go with:-

Where Start_Working_Date Between Cast(DateDiff(Day, -1, GetDate()) as Date) And Cast(GetDate() as Date)

GetDate() returns a DateTime which isn't really what you want as you're concerned with whole days so we cast it to a date to remove the time part. It's then simply a matter of making sure Start_Working_Date falls between the first date your interested in (DateDiff(Day, -1, GetDate()) will give you "yesterday") and the last date you're interested in (GetDate() gives you "today")

And this is all before we have even started to even talk about over night hours ... where a shift begins at 23:00 on day 1, and ends at 07:00 the next day... talk about wonky calculations!
Trust me, you'll want only two fields, a start time and and an end time, both field s should be a datetime type and both should include the date and time of their respective start and end times of the shift. Don't try to separate them out.

-tg

[BONITO]

I got it!

this will query only the data that has the current DATE and previous DATE in column Start_Working_Date.I made this to my project email alert program. all data that has current date and previous date will show in table and alert to email. but if the user closed the schedule in (another program) the Start_Working_Date must auto reschedule (every 3 month)
hope it helps.

  void Display()
        {
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
               // cmd.CommandText = string.Format("select Feeder_Name,Start_Working_Date,Working_Time_Start,Working_Time_Stop,Status,Description,dateadd(Month,3,Start_Working_Date)as Next_Schedule from PM_Schedule where CONVERT(varchar(10),Start_Working_Date,104)=CONVERT(varchar(20),GetDate(),104)  order by Start_Working_Date", con);
                cmd.CommandText = string.Format("Select  Feeder_Name,FeederNo,Start_Working_Date,Working_Time_Start,Working_Time_Stop,Status,Description,dateadd(Month,3,Start_Working_Date)as Next_Schedule  from PM_Schedule Where Start_Working_Date <= '" + DateTime.Now.ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("En-us")) + "' And Status='WAIT' ORDER BY Start_Working_Date DESC ", con);
              
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    dataGridViewFeederSchedule.DataSource = dt;
                    reader.Close();
                    con.Close();
                }
                else
                {
                    MessageBox.Show("No Data");
                  con.Close();
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.ToString());
                con.Close();
            }
        
        }

[FunkyDexter]

Stop. That's a bad solution. You should never concatenate values into sql strings like that. It leaves you open to injection attacks which any hacker can use to steal your data and destroy your database. If you need to insert values from .Net code into sql you should use ADO.Net parameters. JMclllhinney wrote a good tutorial on how to do this here.

However, you don't need to insert values into the sql at all. You have the GetDate() function that will natively get you the current time. As far as I can tell the only reason you've inserted it from .Net is that you're struggling with the conversions and datatypes available to you in SQL Server but instead of dealing with that small lack of knowledge you've sought out an obtuse and dangerous alternative. Here is how you would do the same comparison as you're currently doing using the native sql functions:-

Where Start_Working_Date <= Cast(GetDate() as Date)