.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

Formatting datatable datetime field

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :ASP.Net
I am getting an error message:Conversion from string "mm/dd/yyyy" to type 'Integer' is not valid.  What is best way to format date filed from datatable without showing the time?   ClosingDate is smalldatetime in my SQL DB. ThankslblClosingDate.Text = dt.Rows(0).Item("ClosingDate").ToString("mm/dd/yyyy")

View Complete Post

More Related Resource Links

Comparing Datetime field in DB?

I need to check the datetime field in my DB to see if the time that's current set is 12hr's past? What's the command to use?  Can I just DATEDIFF? 

Flat file upload: Error when inserting into datetime field.

Hi All I am having an issue with uploading a txt file into an SQL table and one of the fields needs to go into a datetime column. The issue is with column3 I have been into the advanced editor of the flat file source and set the output column to decimal and it fails. The error I am getting is below: Error: 0xC02020A1 at Materials Transfer, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Materials Transfer, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Column 3" (48)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 3" (48)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. Error: 0xC0202092 at Materials Transfer, Flat File Source [1]: An error occurred while processing file "D:\Development\r3_downloads\mat.txt" on data row 1. Any help would be great.   Thanks BigGopher

Showing the value as blank in a grid view when the Datetime field in SQL has its minimum value

Hi,Developing an application in C#.Retrieving data using SQL server. A record has a datetime field( eg: Resigned Date). If the datetime field in null in my code I will have the minimum value stored in the database which is "1/1/1900" ( eg: for current employees as they do not have a resigned date this field will have the 1/1/1900 value as I cannot leave this blank)Now when I'm displaying the employee records in a gridview in my application it shows the 1/1/1900 value for the "Resigned Date" field. I need this to be blank.Here is the code that I have done so far...void GetRecords(){DBConnect db = new DBConnect();DataSet ds = new DataSet();ds = db.getDataForgrid("Select * from Employee");DataTable dt = ds.Tables[0];GridView1.DataSource = dt;GridView1.DataBind();}Please help.Thanks

Set the DateTime field using javascript

Hi everyone, I need Help in implementing a solution. I have 2 fields: Resolution Date (DateTime) and Severity (Dropdown) and I need depending on the choise in dropdown (0-3) set different dates into the DateTime field. The difficult part is that I can't realize how is to SET the date there %) Here is my code: var ExpDateResolved = $("input[title=Expected Resolution Date]").first(); $("select[title=Severity]").first().change(function(){ ExpDateResolved.val(''); var chbReqSevDom = $(this).get(0); if (chbReqSevDom.selectedIndex == 0)  //first value in dropdown selected { var currentDate = new Date(); DateResolved.val(zeroPad(currentDate.getDate(),2) + '.' + zeroPad((currentDate.getMonth()+1),2) + '.' + currentDate.getFullYear()); } } P.S. I've tried to set a date into a Text field and it works.

DataTable to a binary field on data base



Is it possible to convert a datatable into binary data and save it into on a binary field on database?

Onchange event for datetime field in javascript is not working?


I want to compare start date and end date when start date is changed. If start date is big, on change event i need to make the end date the same as start date.

So I wrote On change event for start date. But it doesnot work  

getField('input','Start Time').onchange = function() {showEndDate()};


function showEndDate()



var date1 = getField('input','Start Time');

var date2 = getField('input','End Time');


but its not firing at all. So you guys have any idea or any other way to do for datetime on change event?

Return year only from datetime field


Using SQL Server 2005.

I need to return the year only from a datetime field and then group by that field, does anyone know the syntax for this?



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.

create an index on just the date part of a datetime field


Good Morning

is it possible to create an index on a datetime field that just looks at the date part,

as I am constantly querying the table, using a where clause like this


or is there an more efficient method of achieving the same result?







How to query a datetime field in access database using ASP.NET



I am using an Access database with one of the Table columns defined as Date/Time field.


I am trying to use ASP.NET 4.0 Web forms application to query and retrieve the row based on the DateTime field.

Here is my code snippet:

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      DateTime dt = new DateTime(dtBatchCreation.Year, dtBatchCreation.Month, dtBatchCreation.Day, dtBatchCreation.Hour, dtBatchCreation.Minute, dtBatchCreation.Second);

      //string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = " + String.Format("{0:#yyyy-MM-dd hh:mm:ss tt#}", dt);
      string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = @BatchDate";

      string s = @String.Format("{0:yyyy-MM-dd HH:mm:ss}", dt);

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      //cmd.Parameters.AddWithValue("@BatchDate", s);


Formatting a field on ASP.NET -> Excel export




I am exporting a datagrid to Excel sucessfully using this method:



Response.Buffer = True

Response.ContentType = "application/octet-stream"

Response.AddHeader("content-disposition", "attachment;filename=Conference.xls")

'Response.ContentType = "application/vnd.ms-excel"

Response.Charset = ""

Me.EnableViewState = False

Dim oStringWriter As New System.IO.StringWriter

Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)




Problem in properties.AfterProperties with respect to DateTime field.

Hi all,

I have an event handler, ItemAdding and I am trying to get start and end for "Calendar List".

For,  StartDate = Convert.ToDateTime(properties.AfterProperties["EventDate"]); its showing next day.
For e.g, User took 5 day vacation from 12 Jan to 16 Jan. But for StartDate its showing 13 Jan and also for end date i.e. 17th.

I tried some reserach on about this issue: 

But in my case, properties.ListItem is null.
SPListItem currentItem = properties.ListItem;
DateTime beforeValue = System.Convert.ToDateTime(currentItem.Fields[displayName].GetFieldValueForEdit(currentItem[displayName]));
properties.AfterProperties[internalFieldName] = SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforeValue.AddDays(1)).ToString();

This is my complete code for ItemAdding

Formatting DateTime correctly for XML


I need to format the current datetime to inject into an xml document in 'xsd:dateTime' format.  How do I do that here?

Dim FileName As String = HttpContext.Current.Server.MapPath("~/specs/xml/sample.xml")
            Dim xmlDoc As XmlDocument = New XmlDocument()

            'getting element
            Dim lNode As XmlElement = xmlDoc.GetElementsByTagName("LNode")(0)

            'setting the current date attribule on LNode
            lNode.SetAttribute("CurrentDateTime", Now.ToString("YYYY-MM-DDTHH:MM:SS"))

            'lNode.SetAttribute("CurrentDateTime", XmlConvert.ToDateTime(Now.ToString, 0)) 'doesnt work either

Any idea how I format this correctly?

DateTime formatting extension method


Notes: I also updated the pieces of code at http://www.extensionmethod.net/Details.aspx?ID=393

In current project, I get some troubles in DateTime class, about format the date time object with the pattern specific string and the current culture, so I decide to code some extension methods in DataTime class in .NET library. And I think it's it very useful if somebody can use it as right way. I take some idea from this link and coding the enum class for its. After that I also used the Lambda Expression for pass the parameter into the DateTime formatting method. It's really easy for read code. And below is my implementation for that:

+ DateTimeFormat enum class:

    public enum DateTimeFormat

BCS - Datetime field and time zones


I have an external list that has a datetime column. I get the value for this column from my SQL server database.

The value in the database is '2010-10-30 17:00:00.000', but when the value is displayed in my external list it is converted to '2010-10-30 18:00:00.000'. This is done because the time zone for my sharepoint site is set to UTC+1.

I do not want SharePoint to convert the datetime. I want the value in external list to be '2010-10-30 17:00:00.000' no matter what the time zone is set to on my site.

How can I do that?


How to Query only Date Portion of Datetime field


Hi All

i have one data base field called CreatedDate whos Type is DateTime. Let say i have following Data in this Field

10/7/2010 12:07:57 PM

10/7/2010 12:09:14 PM

10/8/2010 01:02:34 AM

now if i pass date 10/7/2010 to my above table i'll not get any record. what is the best way to query only date portion of DateTime field

Localized Datetime formatting using select query

The article Localized Datetime formatting using select query was added by pankaj.sharma on Monday, March 14, 2011.

IntroductionWhen and how to use sql formatting functions to format datetime values when it is being used by the application that is target to be run on different locale and culture.Problem StatementIf schema of a TimeTable is as follows-Activity StartDate
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