Convert DateTime using SQL

Posted: Jan/22/2011 By: nikhil Points:15 Category: .NET - ASP.Net Views:216 Vote Up (0) Vote Down (0)

Hi,

I have a DateTime value in a Table as follows:

2011-01-22 14:30:09.043

I need the output of as follows 22-Jan-2011 2:30 AM/PM

How can we convert by using Sql query

PS.Shakeer Hussain

View Complete Post

Comments:

Be the first to comment this post.

Post Comment

Please login to post your comment

More Related Resources

Calling javascript funtion: Trying to convert a simple example from html to asp.net

I have this javascript function in a javascript file called Bing.js

function onGeocodeClick() {
    myMap.Clear();
    address = document.getElementById("txtWhere").value;
    StartGeocoding(address);
}

function StartGeocoding(address) {
    myMap.Find(null,    // what
              address, // where
              null,    // VEFindType (always VEFindType.Businesses)
              null,    // VEShapeLayer (base by default)
              null,    // start index for results (0 by default)
              null,    // max number of results (default is 10)
              null,    // show results? (default is true)
              null,    // create pushpin for what results? (ignored since what is null)
              null,    // use default disambiguation? (default is true)
              null,    // set best map view? (default is true)
              GeocodeCallback);  // call back function
}

I have this line in my masterpage referencing my javascript file called Bing.js

<head runat="server">
    <title>Geocoding in Bing Maps</title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />
      <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3"></script>

     <script src="Scripts/bingMap.js" type="text/javascript"></script>    <---***bingMap.js        
    <asp:ContentPlaceHolder ID="HeadContent" runat="server">
    </asp:ContentPlaceHolder>
</head>

I have this in my Default.aspx. I have my new asp.net textbox and asp.net button above the htm text and click from the bing example

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <div>
        Address:
        <asp:TextBox ID="txtHere" Width="255px" runat="server" 
            ontextchanged="txtHere_TextChanged"></asp:TextBox>
         
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    <div style="font-family:Arial; font-size:10pt">
          Address: 
          <input id="txtWhere" type="text" style="width:255px" />
           
          <input type="button" value="Geocode" onclick="onGeocodeClick()" />
       </div>
       <br />
       <div style="position:relative;width:400px;height:400px;" id="mapDiv">
       </div>
       <div id="resultsDiv" style="font-family:Arial;font-size:8pt;position:relative; top:-400px;left:425px;width:400px">
       </div>
</asp:Content>

Then this is my code behind for default.aspx.cs

      protected void Button1_Click(object sender, EventArgs e)
    {
        //myMap.Clear();
        //address = document.getElementById("txtWhere").value;
        //StartGeocoding(address);
        string address = txtHere.Text;
        // Get a ClientScriptManager reference from the Page class.
        ClientScriptManager cs = Page.ClientScript;

        // Check to see if the client script is already registered.
        if (!cs.IsClientScriptBlockRegistered(this.GetType(), "StartGeocoding"))
        {
            cs.RegisterClientScriptBlock(this.GetType(), "StartGeocoding", "StartGeocoding(address)");
        }
    }


On Button1_Click(), instead of calling StartGeocoding(address)...it prints the words StartGeocoding(address) at the top of the screen!

Is the ClientScriptBock above suppose to go into Page_Load? How do you make the javaScript call to StartGeocoding(address)?

Thanks, John Feeney

Convert MM/DD/YYYY to YYYY/MM/DD

hello,

When the user enter the data in the text field, they are entering as 12/1/2010 4:19:49 PM.

But in database, the date is in this format 2010-12-01 16:19:49.367.

Here I have to change date format and Convert 12 hr format to 24 hr format.

Can any one help me.

TIA

Problem on DateTime class

i tried to get current date and time using

              DateTime dtCurrTime = DateTime.Today;

            //string d
            Label1.Text = dtCurrTime.ToString();

            //DateTime dt = DateTime.Now.Hour;

            Label2.Text = DateTime.Now.Date.ToString();

i'm getting the date correct but time is always getting default value

12/15/2010 12:00:00 AM

to display current datetime

hi all,

actually i am creating windows application. i done one report form,so here i need to display the current date and time(rdlc file) i am using c#.if anybody know please send me some idea..

Thank You,

Arunabathan.G

DateTime custom managed property search fails with " Your query is malformed. Please rephrase your query. "

I've created a front end search page in Silverlight 4 for SharePoint Search. Currently we are using WSS 3.0 + Search Server Express 2008 (v 12.0.0.6421). The page interacts with the MOSS version of the search web service at: <site collection>/_vti_bin/search.asmx

The reason for this approach is the unique way the requirements specify the look and behavior of the results. The built-in Search Center page or web parts will not satisfy these requirements.

We are going into production but I still cannot figure out how to search against my custom managed properties that are of Type DateTime. If I cannot get this to work, it is a showstopper.

I've tried numerous variations on the SQL Syntax and cannot get it to work. I know the error is in the date portion of the WHERE clause since I can remove it and searches work fine.

Here is a working query:

SELECT Size, Rank, Path, Title, Description, Write, RequestSubmittedDate, DocumentType, RequestPromisedDate, ProblemDescription, DatePublished, RequestOwner, RequestingDepartment, DocumentAuthor, ProjectNumber, ProjectDescription, RequestStatus FROM scope() WHERE CONTAINS ('"nmr"') ORDER BY "Rank" DESC

yet if I try to use a date literal in the WHERE clause as in the following query modification, I get the "malformed" error.

I've tried several variations such as:

AND "RequestSubmittedDate" >= '2010-01-15 05:00:00'

AND RequestSubmittedDate >= '2010-01-15 05:00:00'

Is this a known issue with WSS 3 / Search Server 2008? If so, does it work under 2010?

I've tried downloading the SharePoint Search Service Tool from CodePlex (by Steve Paschka and Larry Kuhn) and it demonstrates the same behavior. I suspect there is something wrong server side that cannot handle DateTime queries but I'm not sure.

*** UPDATE ***

I went to the SearchCenter page, updated the Properties XML in the Web Part properties to add my RequestSubmittedDate field into the fields list. I entered an Advanced Query looking for items where RequestSubmittedDate < 2010-08-15 and received the same error. See the ULS logs in the 12 hive:

High      Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL from scope() where freetext(defaultproperties,'+nmr') And ("scope" = 'All Sites') And (RequestSubmittedDate < '2010/08/15 05:00:00')
Monitorable <Exception><HR>0x80040e14</HR><eip>000000006EA2FB7C</eip><module>d:\office\source\search2\ytrip\tripoli\icommand\qryspec.cxx</module><line>1799</line></Exception>
Monitorable In CRootQuerySpec::Execute - caught exception: 0x80040e14, translated to: 0x80040e14 - File:d:\office\source\search2\ytrip\tripoli\icommand\qryspec.cxx Line:767
High      Log Query: Your query is malformed. Please rephrase your query. More Information: One or more errors occurred during processing of command.
Monitorable Error while executing web part: Microsoft.Office.Server.Search.WebControls.SrhdcGenericException: Your search cannot be completed because of a service error. Try your search again or contact your administrator for more information. ---> Microsoft.Office.Server.Search.Query.QueryMalformedException: Your query is malformed. Please rephrase your query.     at Microsoft.Office.Server.Search.Query.FullTextSqlQuery.Execute()     at Microsoft.Office.Server.Search.WebControls.SearchResultHiddenObject.GetResultData()     --- End of inner exception stack trace ---     at Microsoft.Office.Server.Search.WebControls.SearchResultHiddenObject.GetResultData()     at Microsoft.Office.Server.Search.WebControls.SearchResultHiddenObject.get_ResultsReturned()     at Microsoft.Office.Server.Search.WebControls.C...
*** END OF UPDATE ***

Convert existing form published to form library to content type

Hello,

I have a form that I publish directly to a form library template that I would like to start using in other places as a content type. I saved the form as a content type and added the content type to the existing forms library. Unfortunately, only half of the forms are populating the content type columns (they populate the existing library columns as well). The half that populate the new content type columns were saved before a certain date. This isn't a refresh issue as I can open the newer forms and go back to the forms libary and see the columns are still not populated. I've tried creating a new forms library with the new content type and the behavior is the same for forms saved after a certain date. Anyways, I would like to use the exisitng forms libary to maintain workflow history.

1. How can I get all of the existing forms to populate the new content type columns?

2. If possible, can I unlink the exisitng forms from the static forms library template to this new content type (In other words have changes to the content type form update the exisitng forms)?

Thanks

convert multiple line text to single line text

Hello,

I have imported a spread sheet into share point from Custom List / Import Spreadsheet.

Some of the lines are converted to Single Line Entry and some others as Multiple Line Entries,

My Question is:
1. Can we convert that multiple line entry into single line entry.
2. Can we create columns first and then import the spread sheet.

Please help.

With regards
Repath Athyala.

Love the Love that Loves the Love and Hate the Love that Loves the Hate.

Convert Number as string to Integer

Hi,

I have a listbox with items selected from a list which holds numbers with decimal points. I should convert this to Integer

I tried using Convert.ToInt32 but it is giving error as it is expecting only whole numbers.

How to convert the decimal numbered string to a proper Integer...? Please help

External List & SPListItemCollection.GetDataTable & DateTime

Hi!

Using Office SharePoint Server 2010 (Build 14.0.5128.5000). There External Content Type (defined methods Read Item and Read List) for submission to the SQL server with the following contents:

select
 dt = getdate
();

Further, the written code of the Web Part to read:

DataTable GetDataTable(SPList list, SPQuery query)

{

 SPListItemCollection items = list.GetItems(query);  

 return
 items.GetDataTable(); // (1)


}

At the point (1) code coincides with the exception of:

[FormatException: String was not recognized as a valid DateTime.]
   System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) +4588098
   System.String.System.IConvertible.ToDateTime(IFormatProvider provider) +56
   System.Data.Common.DateTimeStorage.Set(Int32 record, Object value) +113
   System.Data.DataColumn.set_Item(Int32 record, Object value) +67

[ArgumentException: String was not recognized as a valid DateTime. Couldn't store <2010-12-01 23:29:28:557> in dt Column. Expected type is DateTime.]
   System.Data.DataColumn.set_Item(Int32 record, Object value) +6211458
   System.Data.DataTable.NewRecordFromArray(Object[] value) +6214475
   System.Data.DataRowCollection.Add(Object[] values) +21
   Microsoft.SharePoint.SPListItemCollection.GetDataTableCore(Boolean isJsGrid) +949

After examining the package Microsoft.SharePoint.dll have the following procedure call:

SPListItemCollection.GetDataTable -> SPListItemCollection.GetDataTableCore -> SPListDataSource.GetFilteredEntityInstances -> SPListDataSource.GetFilteredEntityInstancesInternal -> SPListDataSource.ConvertBdcValueToSafeArrayValue.

And there are rows (see picture ):

internal static object ConvertBdcValueToSafeArrayValue(SPFieldType fieldType, SPWeb web, 

 bool bFormatDates, bool bUTCToLocal, bool bConvertDateTimeToString, bool bThrowException, 

  object inputVal)

{

 object obj2 = null;

 if ((inputVal != DBNull.Value) && (inputVal != null))

 {

  SPFieldType type = fieldType;

  if (type != SPFieldType.DateTime)

  {

   if ((type == SPFieldType.Boolean) && (inputVal is bool))

   {

    obj2 = ((bool) inputVal) ? "1" : "0";

   }

  }

  else if (inputVal is DateTime)

  {

   DateTime date = (DateTime) inputVal;

   if ((date < DateMinSuported) || (date > DateMaxSupported))

   {

    string output = SPResource.GetString("ExternalDataLists_DateTimeOutOfRange", new object[] { date.ToString(CultureInfo.InvariantCulture) });

    ULS.SendTraceTag(0x63333369, ULSCat.msoulscat_WSS_General, ULSTraceLevel.Verbose, output);

    if (bThrowException)

    {

     throw new SPException(output);

    }

    return null;

   }

   if (bFormatDates)

   {

    obj2 = SPUtility.FormatDate(web, date, SPDateFormat.DateTime, bUTCToLocal);

   }

   else

   {

    DateTime time2 = date;

    if (bUTCToLocal)

    {

     time2 = SPContext.GetContext(web).RegionalSettings.TimeZone.UTCToLocalTime(time2);

    }

    if (bConvertDateTimeToString)

    {

     obj2 = time2.ToString("yyyy-MM-dd HH:mm:ss:fff", CultureInfo.InvariantCulture); // (2) MAY BE THERE "YYYY-MM-DD HH:MM:SS.FFF" ?

    }

    else

    {

     obj2 = time2;

    }

   }

  }

  if (obj2 == null)

  {

   obj2 = Convert.ToString(inputVal, CultureInfo.InvariantCulture);

  }

 }

 return obj2;

}

It turns out that in Microsoft.SharePoint.dll point (2) is likely to mistake it should be:

How to convert Filestream to string[]

Hi all,

is there any chance to convert a filestream to an array of string without read it line by line ?

Thanks