.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Date table help

Posted By:      Posted Date: October 26, 2010    Points: 0   Category :ASP.Net

First I nee to create a table that shows only weekend dates DD MM YYYY

Second I need to create a table that shows only federal holidays by date DD MM YYYY

Third I need to compare dates that are in my table called ( hist_end and hist_start) against the weekend dates and holiday dates.

If a weekend date or holiday date falls anywhere with in the hist_start to hist_end that date it gets subtracted from the line below

Forth I need to take the dates from hist_end and subtract them from hist_start to come up with a number then add one to it so the date starting is truly counted and then multiply it by 9 for a nine hour work day but if a weeked date or holiday date falls in this time subtract theses dates as needed.


I would like it in SQL format so I can plug it back in to the present asp page that the users are accustom to


This is as far as I got before the change request was asked of me to do larger start and stop date.  They had first requested just to subtract weekends from a 7 day start and stop date.  Now they want it to look at the total start and stop dates and determin if any of the dates are weekend or holiday day and subtract them from the total count.

SQLStmt = SQLStmt & " CASE WHEN (trunc(b.HIST_END) - trunc(b.HIST_START) + 1) = 6 THEN ((trunc(b.HIST_END) - trunc(b

View Complete Post

More Related Resource Links

How to insert the Current date and time in to SQL Table..

Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

SSRS Table hide duplicate by date

Hi, I am parikshit deshpande i am working in SSRS 2005 from last three months. I am trying to solve this problem please help me for that, I have one table as per  Date                     Shift                               Tons                       Passes 1/1/2009            Day                                    Formula                  Formula 1/1/2009           Swings                            1/2/2009           Day 1/2/2009     &nb

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Problem displaying Date from Sql table on Calendar Control

Hi, I am displaying Event dates from SQL table on Calendar Control in ASP.NET. Also, I have GridView Control which shows event details when certain date is clicked on calendar control. But I have a problem as not all dates are displaying properly. Strangly enough only dates which have same month and day date are displayed. For example: It shows ok dates such as 08/08/2010, 09/09/2010, 10/10/2010 etc. If I click on the date which in SQL table exists as 11/25/2010 or 12/15/2010 etc (no matching month/day numbers) it shows error message saying: "System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string."  Follwoing is my code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.Sql; using System.Runtime.Remoting.Messaging; using System.Configuration; using System.Data.SqlClient; using System.Data; using System.Drawing; public partial class Test_Calendar : System.Web.UI.Page { SqlConnection mycn; SqlDataAdapter myda; DataSet ds = new DataSet(); DataSet dsSelDate; String strConn; private void Page_Load(object sender, System.EventArgs e) { strConn = "Data Source=mydatasource;Initial Catalog=DBname;Persist Security I

select top row for multiple entry for a date in table


My current query is

select  Date= createdon , Total=count(*) from ReportDetail where 
reportid = 9 and (CreatedOn BETWEEN '07/01/2010' AND '09/30/2010') 
group by  createdon
order by createdon desc

return data like

2010-09-21 09:36:46.493    112
2010-09-21 08:33:12.667    114
2010-09-21 07:45:20.830    176
2010-09-21 07:33:34.340    114
2010-09-20 07:27:43.753    125
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

What I am looking for

2010-09-21 09:36:46.493    112
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

Basically for 9/21, I want to get only one latest row. Please advice.

How to retrieve maximum date from table?


Im getting problems with trying to retrieve the maximum date value from a table



The result table is shown below as you can see there are duplicate STUDENT_NO for 123, why is this? 

Create Table with expiry date



I just need to know if I can create a table which will automatically get dropped after N number of days. The reason is I create lots of physical tables in side sp's to store the logging and mail it, when there is any issue that table is retained in the database. I know that this can be replaced with global tables (##table_name). But just need know to if I can create table like this. Can any one know how to do this?

Thanks is Advance,


Multiple companies and fiscal calendars in date dimension table

We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.


Thanks in advance,


Hamlin Stephen

query wich returns records for each day between start date from table 1 and mutation dates in table



I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?

Problem inserting a null value into date column in SQL table


Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

Finding the minimum date for every month in a table.



I have a table that stores the date for every day of the month. I need to get the minimum date for every month thats in the table. So, my table shows the report as below

SSAS date-range ownership of assets (many-to-many table)


The basis for this post is here: http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/b586e9cb-2e4e-4bc8-be33-0b80d57c13bc

A many-to-many relationship exists in the database to record the ownership of an asset. 

The result I want to produce in the cube is (Company is divided by pipes, we see comp A owend asset 1 in 1996, 1997, 2002 and 2003; Company B in 1998, 1999, and 2000): (imagine data for each column is financial)

Asset 1
Company A         | Company B                    |  Company C    |   Company A     |     Company D
1996        1997    | 1998      1999    2000      |  2001               |   2002               |       2003


Returning record from History Table as it would have existed at a specific date/time


I have a table that uses a trigger to save changes (old values) to a history table. This process is working fine, and I have reports that detail the history of a record for the end user. My problem is that the end user now wants a report that will retrieve how the record looked at a specific date/time.

Source Table:

Id	First	Last	Active	Date
1	John	Doe	1	10/25/2010
2	Jane	Smith	1	10/25/2010

History Table:

Id	SourceId	First		Last	Active	Date
1	1		Jonathon	NULL	NULL	5/1/2000
2	2		NULL		Gray	NULL	10/1/2000
3	2		NULL		Smith	NULL	6/15/2003
4	2		NULL		Doe	NULL	11/23/2008

If the user queries for SourceId 2 on 1/1/2009, they need to get:

Id	First	Last	Active
2	Jane	Smith	1

I've used COALESCE searching multiple fields, but I need to figure out how to

Get selected date value from DropDownList control and pass it to SQL table



This must be something not very difficult but I am pretty new in C# and have a problem to resolve.

I have tree dropdownlist controls. One of the control values are dates. When client selects all three drop downs then I am passing values to SQL table. Everything works but one. Date value shows 1900-01-01 instead of selected value in SQL table. I am not passing date value correctly but can't figure out how to do it.

Following is a code:

                            <asp:DropDownList ID="ddlLocation" runat="server" AutoPostBack="True" 
                                DataSourceID="SqlDataSource3" DataTextField="ShowLocation" 
                            <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
                                ConnectionString="<%$ ConnectionStrings:myconnection%>" 
                                SelectCommand="SELECT * FROM [ShowLocation]"></asp:SqlDataSource>

Reformatting variable character date field in custom table


I have a custom table with a variable character field storing a date. It is currently in the format YYYY/DD/MM plus a time stamp. I want it in the format MM/DD/YYYY without a time stamp.

I've tried a few CONVERT and UPDATE statements, with no change. The syntax is okay, and in some cases the statement has an effect (such as cutting off all but one character of the time stamp), but does not reformat the date. I've also tried resetting the fields as DATE fields or SMALLDATETIME fields in the custom table, and I receive errors after doing that.

Reformatting variable character date field in custom table


I have a custom table with a variable character field storing a date. It is currently in the format YYYY/DD/MM plus a time stamp. I want it in the format MM/DD/YYYY without a time stamp.

I've tried a few CONVERT and UPDATE statements, with no change. The syntax is okay, and in some cases the statement has an effect (such as cutting off all but one character of the time stamp), but does not reformat the date. I've also tried resetting the fields as DATE fields or SMALLDATETIME fields in the custom table, and I receive errors after doing that.

Need to comapre date and update the table?



I have requirement , which reads data from a CSV file, and checks if the same data exist on the table it needs to upadate a flag.

please let me Know how i can go abt it



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend