.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

Calculated Date Fields

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
I'm trying to calculate whether a delivery is on time or late and by how many days. Is there a way to format the column so it show's the result in the number format as opposed to showing up as a date? Negative numbers don't export into excel well.

View Complete Post

More Related Resource Links

How to handle *many* date fields?

I have put together a cube for auto loan analysis. It is populated via monthly snapshots. There are many actions taken on these loans that have a date stamp associated with them (repossess/resell/refinance, etc.). These fields are on each row and only populated if utilized, else NULL. In the design of the cube, all of these fields were specified as being associated with date dimensions. Now that I am actually building this thing out, my date dimension is aliased (role playing) 15 times! This seems ridiculous to me as many of these date fields are not useful for analysis, per se. Primarily the analysts need the ability to filter based off of them. Ie. Display measures [x],[x1] in a pivot table where reposession date > '[Jan 2005], etc. They use excel 2007 for analysis. Is there another way to handle all of these auxiliary/sparsely populated dates besides creating dimensions for them?   Thanks for your help!

Change database fields automatically according to date

Hi, I'm currently developing a software application for my client. Part of the project is to keep track of his stock's shipping status. The client must insert the stock's pick-up date, expected time of departure (ETD) and expected time of arrival (ETA). The stocks will be called out using Grid View and the status, being one of the keys of the database, will be shown. Before the current date reaches pick-up date, the status must show "in warehouse", and after the current date reaches pick-up date and is before ETD, status must show that its "on the way to the port", and after ETD and before ETA, "in freight", and finally after ETA, "arrived". What datatype should I use for the database and where should I program it so that it changes automatically accordingly ?

Updating a List item field value in a SPD Workflow is setting Date fields to null

Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Is it possible to use multiple date fields to have an event show more than once on a Sharepoint 2007

Hello, Is it possible to use multiple sets of start and end times to have events display multiple times in a sharepoint calendar? To clarify: - I have the standard fields of "Start Time" and "End Time" in my calendar list. - Events show on the calendar just fine. - Would like to create additional fields called "Start Time 2" and "End Time 2" within the same record and have the event show multiple times on the calendar. - The recurrence functionality of sharepoint will not meet my needs because the pattern I need to use is not in one of the allowed choices. Thanks in advance, John

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

Calculated Fields and Lists.. do tell!

Ok, so I have a massive Excel Spreadsheet with some lookups and calculated fields.  I need to port this over to a SharePoint list.   I'm guessing for something like this the Import Spreadsheet function won't cut it.. and that I have to rebuild the spreadsheet essentially from scratch and start from there. Is this the case? Once I create the columns as I want them, can I still port over the information (data) in the spreadsheet or am I essentially pooched when it comes to porting that data over?    Thanks!   J

Weird Date vs Date/Time issue using a calculated column

I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

Why can I not group by calculated field in Date format?

After migrating to SP2010 Foundation, I've discovered that for some reason I can't group a list by a calculated date field. Ex: Create a list with a date column A. Create another column B with a simple calculated date of [A]+1 and use the field format type 'date' Modify view to group the list by column B.  You're list will load, but when you attempt to expand one of the groups you'll be greated with an exception error. Why?? Am I doing something wrong? Note: You can group fine if the data type for column B is anything except 'date' format, but I really need to group items by a calulated date. Ultimately, what I'm trying to do is group my list items by week by calculating the end of the week using a simple formula =[Must ship by]+7-WEEKDAY([Must ship by])  then I want to gropu by the [Must Ship by] column like I always did in SP 2007, but now it errors out.  I also wanted to create my own timesheet template since the Fab 40 templates aren't available for 2010, but I can't really do that effectively without being able to group timesheet entries by week.  

Types of calculated fields in a query

Hello, I have a question about how the SQL server engine determines the types of calculated fields. For simple calculations I assume it's based on the types of the fields composing the query as they are defined in their tables, but what about a calculated field in this form, for example: isnull(field-name,-99)? Is there a way I can change the engine's selection in the query? Thanks.

Types of calculated fields in a query

Hello, I have a question about how the SQL server engine determines the types of calculated fields. For simple calculations I assume it's based on the types of the fields composing the query as they are defined in their tables, but what about a calculated field in this form, for example: isnull(field-name,-99)? Is there a way I can change the engine's selection in the query? Thanks.

How to sum up InfoPath calculated fields

I have a repeating table with calculated fields on each row.  At the end, I need to sum up all these calculated fields into total.  Which total is also a calculated field to sum up 2 calculated fields in the repeating table.  The total never show anything after data entry.  How can I sum up total from the calculated fields?  For example: QTY 1 of a Computer would cost $1000.  In the repeating row, I will allow customer to fill in multiple rows.  A field named line item total is a calculated field named EXTCOST which is set to QTY X Computer = $1000.  At the bottom of the form, I would like to sum up EXTCOST to get the Grand Total Cost.  The Grand Total Cost is fomat as SUM(EXTCOST) which is zero at all time. Please advise. Brian.

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.

Calculated Measure Help - Comparing Months from two Date Dimensions

Hello:   I have a Measure Group that has two Date dimensions, a Reporting Date and a Fee Paid Date. The Reporting Date and the Fee date is based on the same Dimension, which has a standard YQMD hierarchy. It's very typical. The calculation is called "Fees Paid In Same Period", and it would display the Fees paid if the Reporting Month equals the Fees Paid Month.   How do i do this?   Thanks,

Calculated Column: Text ==> Date and Time Column (Date+Time)





I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)


How can I have a calculated column which is a Date+Time based on a text_column

Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time



20070629_0848 converting to 29/06/2007 08:48


==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)


Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...


It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....





Formatting Date consisting of several Fields but no value for the day



     I´m working on some reports with SSRS 2008 and I need to display a date in the format dd.mm.yyyy. Normally that would be easy but the Problem is, that the Date I need to display doesn´t exist as a Field. I have a Field containing the month and another Field containing the year but no field containing the day. Furthermore the day should be always the last day of the month. Does anybody know how to solve this date-puzzle?


Your help would be much appreciated.


Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c


I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

Sharepoint calculated fields?


Hi all,


I have a quick question for you in relation to sharepoint calculated fields.


I am trying to set up a method in calculated fields, using the task list

to enter in the status (not started, In progress, Completed, Deferred, Waiting on someone else)


Basically when the user selects t

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