.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

display only the date (not time) from a sqlserver date field in dynamic data

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

I have some Date fields defined in my sqlserver database but these display with a time part as well (00:00:00).

how do i esnure that only the date part is displayed and not this redundant time?


View Complete Post

More Related Resource Links

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

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.

How do I format a date time field in a CQWP to YY-MM-DD ?

Hi. I have been trying to get the date-time to look good for some time now, but have failed. I have a Content Query web part that picks up article pages from a page library. The pages contains a field called 'article date'. I want to show just the 'Title' and the 'Article date'. Doing this default gives me: My title 10-09-03 00:00:00 I want it to be: My title 10-09-03 Is this possible? I'm starting to realize that I probably have to edit the xsl styles for this webpart but if thats the answer, how do I do that? ThanksThomas Balkeståhl - Technical Specialist - SharePoint

Date Time for Display

Hi AllI want to display time on Y axis and days on  x axis.I want to dispaly 12:00 AM,11:00 AM...11:59 PM.  on Y axis.I want function to create sequence like thatand on X axis display week like 6-sep,7-sep,..11-sep.can you give me the function for this.how i can create these

I want to display date field in y-axis of stacked bar, how?

select date   from   employee   now i want to show this date field in y-axis of stacked bar please tell me whats the procedure of doing this?

Setting a date time field to now


Hi all

MOSS 2007 - Visual Studio Sequantial Workflow.

I have a field in an item that I want to update within my Workflow code.

Field Definition:

<Field Type="DateTime" DisplayName="Date Approved/Declined" Required="FALSE" Format="DateOnly" ID="{0c1d9bb7-52f9-4b83-8d26-eda6c9879921}" SourceID=

getting time and date from datetime field in javascript


Hello guys, I need to get hours from the difference between two datetime fields. I wrote the javascript which will get the date from datetime field.

But its not getting the correct time that entered on date time field. Its defaultly getting the current time on my system.

So do you guys have any idea. How to get time from date time fields. and how calculate hours between two datetime fields using javascript.

Workflow SPD: Set Today Date+Time to Column-field




I have a little question about settings specific today (Date + Time) value to a field/column within a workflow that is designed by Sharepoint Designer 2007.


I can manage to set the field to today() => but the time is always 00:00 houres.

I would like to set a specific field to the "exact" date+time when the workflow is running. (so choosing a fixed date+fixed Time isn't a solution either)


So I anyone could help me how I can do such "simple" task, I would be very pleased.






Time added to Detailsview TextBox Date only field


Does anyone know why a time stamp is added to a textbox field that is bound to a SQL 2008 date datatype field?

I can view the data in the database and, as expected, it only contains a date. When I bind the textbox to the db field it tacks the time '12:00:00 AM' to the end of the date?


want to display 24 hours time and date with datatype Datetime (Not String) in SSRS


Hi All,

I want to display 24 hours time and date in SSRS & for that I am using Format(now,"MM/dd/yyyy HH:mm:ss") but I am using this expression in Parameter, where I need to define datatype of that parameter as DateTime, not string. For Expression Format(now,"MM/dd/yyyy HH:mm:ss"), If I use DateTime datatype, It is showing error but working with String datatype.

For example:

I have parameter To Date  and value of this parameter should be 10/11/2010 13:09:16 (To Date: 10/11/2010 23:09:16) , not in AP or PM format and datatype of this parameter, want to keep DateTime that I can select value from Calender.

Please suggest me how I can achieve this?. I don't want to do it in stored Procedure side, want to do it in report side.

Thanks Shiven:)

Discrepancy in Calculated Field using Date/Time



I have a calendar item as part of my MOSS 2007 site and would like to show the events coming up in the next 30 days on my home page.  After some research I understand that I cannot filter on the Start Time field when I try to create a view, so I've created a new field called Event Begin that is a calculated field set to =[Start Time].  I can then create my view for the upcoming events by filtering on the calculated Event Begin column.


This works well, except for events that are marked as All Day.  In this case, the value of the Start Time shows midnight as the time (i.e. 1/11/2008 12:00 AM) which is fine, but the value of the Event Begin column shows the time 5 hours earlier (i.e. 1/10/2008 7:00 PM).  I am not subtracting anything when calculating the Event Begin value, so my initial thought is that I have a conflict between Time Zone settings somewhere.


I am Eastern Standard Time here in the US which is GMT-5:00, which would explain the 5 hour difference.  Unfortunately, I cannot figure out where the incorrect Time Zone setting might be.  Within my site the time zone is correct (under Site Settings >

Date time field in list



I have created a list with data time field. In the date time field,by default in minutes dropdown we are having 0,5,10.... with 5 as interval.

But I need to enter data time field as 11/16/2010 12AM 03.

How to change the intervals in minutes dropdown.


Data validation on a datetime column - or remove date part and only keep time part of the datetime2


Windows Server 2003, SharePoint Designer 2007, Windows SharePoint Services 3.0

I have a calendar list with start date(datetime1) and end date (datetime2).  Each is the default column created by the calendar list in SharePoint.  They both display a date and a time to choose from.

QUESTION:  Is there a way to remove the date section of the end date in a custom list form so that the user can only pick an end TIME?  Or, is there a way to force the end date (datetime2) to HAVE TO BE the same DATE as datetime1, only allowing for a different time?

Thank you!

compare date field in sharepoint designer without considering time



I  set date1 field in sharepoint designer with “set field in current item” action. And in next step I want to compare date1 with another date field. With “if date1 not equal to date2”

although I set  “Date & Time Format” to “Date only”, my problem is sharepoint designer consider time ( although my condition is true ,I think its compare the time of my date field and cant go next step) and I don’t want it. I want to compare date filed only with date,

what should I do, that sharepoint designer only consider Date in Date field in action?

Thanx a lot.

InfoPath 2007 display date for multiple time zones



We have an InfoPath 2007 form integrated with SharePoint 2007 and uses Web Service as Data Sources. The end users are located in multiple time zones in US and Australia. The server is located in US Eastern time zone. We would like the form to display user's local date and time.

The return of web service shows correct local date and time, except the time zone keeps at -04:00. For example during the one hour after midnight Eastern Time, the web services return:

Eastern: 2011-04-13T00:04:03.793537-04:00

Central: 2011-04-12T23:04:15.5734594-04:00

Mountain: 2011-04-12T22:04:24.3693642-04:00

Eastern Australia: 2011-04-13T15:04:18.8366836-04:00

When I preview the form it shows correct date, but after uploading it to SharePoint and view in web browser, the date is off by one day (either ahead or behind).

My looked at this post http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/677c03e4-8d71-43b3-aff9-1b6662a2528a but I don't get how it display different dates in different timezones by using UTC.

How can I make sure the form ignores the server time or the time zone indicated in the web service return, and just display the date as it is?



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