dcsimg
Results 1 to 10 of 10

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    75

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

    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:

    Code:
    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 ?

    Name:  pix.jpg
Views: 176
Size:  15.6 KB

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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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:
    Code:
    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    75

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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.
    Code:
    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
    )

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    Quote Originally Posted by BONITO View Post
    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?
    Quote Originally Posted by BONITO View Post
    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    75

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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.
    Code:
    ----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

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,996

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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:-
    Code:
    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)
    Code:
    (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:-
    Code:
    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")
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,895

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    Quote Originally Posted by FunkyDexter View Post
    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:-
    Code:
    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)
    Code:
    (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:-
    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    75

    Re: How to query data of column Current Date and all Previous Date in sql server 200

    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.

    Code:
      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();
                }
            
            }
    Last edited by BONITO; Nov 12th, 2018 at 09:41 PM.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,996

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

    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:-
    Code:
    Where Start_Working_Date <= Cast(GetDate() as Date)
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width