Select min date value and calculate date value?

Related searches

I have a table called "SUMMARYDATA" which has columns "STATUSIN,STATUSOUT,LATECOME" and I want to select min value on column "STATUSIN". And make a condition: IF MIN(STATUSIN) > "08.00 AM" Then LATECOME = MIN(STATUSIN) - "08.00 AM". How to select min date value? And how to calculate MIN(STATUSIN) - "08.00 AM"?

This is my code to display data from database:

    <asp:Repeater ID="rptrSUMMARYDATA" runat="server">
        <HeaderTemplate>
            <table class="table">
                <thead>
                    <tr>
                        <th>IN</th>
                        <th>OUT</th>
                        <th>LATECOME</th>
                    </tr>
                </thead>
                <tbody>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td><%# Eval("STATUSIN") %></td>
                <td><%# Eval("STATUSOUT") %></td>
                <td><%# Eval("LATECOME") %></td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
                </tbody>
            </table>
        </FooterTemplate>
    </asp:Repeater>  

This is my code on backend:

     private void BindSUMMARYDATARptr()
    {
        String CS = ConfigurationManager.ConnectionStrings["MANHOURConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM SUMMARYDATA", con))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    DataTable dtDaily = new DataTable();
                    sda.Fill(dtDaily);

                    rptrSUMMARYDATA.DataSource = dtDaily;
                    rptrSUMMARYDATA.DataBind();
                }

            }

        }
    }


Can you try this..

  SELECT  STATUSIN,STATUSOUT, CASE WHEN CAST(STATUSIN AS TIME) > CAST('08:00:00' AS TIME) 
           THEN CONVERT(varchar, dateadd(s, -28800 ,STATUSIN), 108)  END AS LATECOME 
  FROM SUMMARYDATA

Select min(date), max(date) and group by day from one column , I have one column containing datetime values. I need to find min and max for every day. The data looks like this 2012-11-23� The below array formulas can help you to find max or min value between two dates in Excel. Please do as follows. 1. Select a blank cell to locate the max or min value. Finding max value between date 2016/7/1 and 2016/12/1: 2. Enter the below formula into it, and then press the Ctrl + Shift + Enter keys to get the result.


Get the MIN from datatable and use DateTime.Subtract

var minIN = Convert.ToDateTime(dt.AsEnumerable().Min(row => row["IN"]));
Console.WriteLine($"LATECOME {minIN.Subtract(minIN.Date + new TimeSpan(8, 0, 0))}");

Note: AsEnumerable is part of System.Data.DataSetExtensions. Install this package from nuget

To Update directly in Database (I would not recommend this, unless it is absolutely needed)

UPDATE SUMMARYDATA 
  SET LATECOME =  CASE WHEN DATEDIFF(s,DATEADD(HOUR, 8, CAST(CAST((select MIN(STATUSIN) from SUMMARYDATA)  AS DATE) AS DATETIME)),(select MIN(STATUSIN) from SUMMARYDATA)) > 0 THEN
  (CONVERT(TIME,(select MIN(STATUSIN) from SUMMARYDATA)-DATEADD(HOUR, 8, CAST(CAST((select MIN(STATUSIN) from SUMMARYDATA)  AS DATE) AS DATETIME)))) ELSE NULL END

Solved: Calculating Min/Max date columns based on selected , Solved: Hi, I am trying to insert 2 new columns into my dataset containing the MIN /MAX values of the date slicer. I was thinking about the� In this case they are date conditions but they can be whatever you like, displayed in cell C3 and E3. The maximum value in that date range is calculated in cell C6, The minimum value in cell C7. There is also a formula that finds these values and return their corresponding date, in cell E6 and E7. Formula in cell C6:


select DATEADD(minute, -iif(CAST(STATUSIN as time) > CAST('08:00' as time), DATEDIFF(MINUTE,CAST('08:00' as time),CAST(STATUSIN as time)) ,0) , STATUSIN) as LATECOME
from SUMMARYDATA

Tableau MIN(Date) LOD's - Use Cases, Tableau LOD's can be used to get the date of first purchase, first value on entering the market we must first calculate the first date (of This calculates the difference in days between the first date and the current selected� Find out min and max Dates and show a value only from this row Hi everybody, i created a table: ID Date Value. 1 02.04.2011 5. 1 12.05.2011 2. 1 23.07.2011 7


Try this:

select STATUSIN,STATUSOUT,case when CAST(STATUSIN as time) > CAST('08:00' as time) then 
DATEDIFF(MINUTE,CAST('08:00' as time),CAST(STATUSIN as time)) 
end as LATECOME from test;

How to find earliest and latest dates in a range in Excel?, Select the range that you will pick up the latest or earliest date form. This Select Cells with Max or Min Value utility of Kutools for Excel also supports rows and separate values by specified marks, or combine rows and calculate their sums,� Summary: in this tutorial, you will learn how to use the Oracle MIN() function to return the minimum value from a set of values.. Oracle MIN() function syntax. The Oracle MIN() function is an aggregate function that returns the minimum value of a set.


How to find max or min value in a certain date range (between two , Select a blank cell to locate the max or min value. Finding max value between date 2016/7/1 and 2016/12/1: 2. Enter the below formula into it, and then� The smallest value. Remarks. The MIN function takes a column or two expressions as an argument, and returns the smallest value. The following types of values in the columns are counted: Numbers; Texts; Dates; Blanks; When comparing expressions, blank is treated as 0 when comparing. That is, Min(1,Blank() ) returns 0, and Min( -1, Blank


Note that I do not want to pull the minimum and maximum values from the underlying table with the date column, I want to pull the dates as selected in the slicer. In this example if I select 1/1/2018 as the 1st date, the first date in my table is actually 1/7/2018. I want to be able to get the 1/1/2018 date value, not the 1/7/2018 value in my data.


SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL