Home » SQL Server

How to remove time field in oracle datetime ?

hi guys,


i am using oracle 10g as database, i have a textbox called Transaction date,

i want to save transaction date into column filed called TDate (Date),

actually i am using ajax calender extender to get date in the format of 23-sep-2010,

when i retrieve the record it shows me like 23-sep-2010 12:00:00 AM

i want to remove the time filed..i used query " select TDate from Tranaction where TID=1"


but it shows like this 23-sep-2010 12:00:00 AM,how to remove the time field




3 Answers Found


Answer 1

You could do that in C#, Lets assume you have the string value of a date  as below:

      string strDate = "23-sep-2010 12:00:00 AM";
      DateTime tempDate = Convert.ToDateTime(strDate);
      string strShortDate = tempDate.ToShortDateString();

you can use methond ToShortDateString on DateType object. 



Answer 2

Yes, its simple as Habib OSU mentioned,


This would display just the date on your text box.

For more information on ToShortDateString() method:






Answer 3

Hi Saravanan2009,

Welcome to MSDN forums!

If your question has been solved, please don’t forget mark/vote the replies which helped you, and this will encourage the other community members to join in discussion and help each one.

Hope these helps, if you have any problems, please feel free to let me know.

Best Regards,
Alan Chen
Please remember to mark the replies as answers if they help and unmark them if they provide no help



I am exporting data from an sql table to an excel spreadsheet in Visual Studio 2008. There is a field that is date/time that I only need the time part of (hh:mm). I am using the Convert command to do this, but I am getting the error 'Undefined function Convert in your expression'. Is there another function I should be using? Or am I missing and Import command at the top?

Here is the code (field TimeofFall is the one I am having trouble with):




Imports System.Data






Class frmReportsMenu

Dim ExcelConnection AsNew System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=" & “C:\test” & ";" & _

        "Extended Properties=""Excel 8.0;HDR=YES""")




        Dim SQL1 AsString = "SELECT FacN, PatNum, Convert(varchar(8), TimeofFall, 108) as TimeofFall, INTO [tblData] FROM [tblData] IN '' [ODBC;Driver={SQL Server};Server=sql;Database=FallsTracking;Trusted_Connection=yes]"


        Dim ExcelCommand AsNew System.Data.OleDb.OleDbCommand(SQL1, ExcelConnection)









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.


I am working on a large data mart project and have source tables with hundreds of millions of rows. 

There have been indices place on each table in the staging area and include the updated timestamp field on most key tables, which is used in many queries.  Because we have high water marks on the source tables for the extracts, the only real place that the update field is being queried is only concerned with the date part and not the time part.

What i am thinking is by stripping out the date part as an integer in the initial extract to staging and indexing on this, the query index search would be looking up a far fewer records in the index and should therefore be a huge amout quicker.  I guess its just a question of how much slower the extract will be with this extra index vs the speed gained downstream.

Just wanted to get feedback on whether in principle this would make sense or other approaches that could be applied.



Iam reading from Oracle DB Using legacy Oracle Adapter. One of the fields is Datetime Format. I want to capture this datetime value in my BAM report. The dateformat that is coming out of Oracle is like this 2010-10-25T18:30:00.000000-00:00 How to convert this into 2010-10-25T18:30:00.000 which is acceptable to BAM ? I want to convert the datetime format coming out of oracle in to string format within map how to achieve this? Thanks
I have a date that is formatted like so: 2010-09-01 00:15:46.00. What is the most elegant way to get the date and the hh:mm. I do not need seconds or milliseconds.

Hi I have a datetime and I want to get the HHMMSS  only

when i use the function I am getting 04:02:01



i have to remove the : from the time.








I am using MsChart for .net 4.0 to plot candlestick chart on datetime x axis

but it shows empty datapoints for saturday and sunday i tried to remove datapoints for saturday ,sunday using

myDatManip.Filter( DateRangeType.DayOfWeek, "0,6", "Series1, Series2" );

but it still shows empty datapoint thus showing gap between two candles in my actual chart

is there any way to remove gap between candles

(i dont want to use isIndexed=true)

my x axis is datetime and y axis is number



The code-snippet looks like:

<form id="setTime">

<field name="newTime" type="time">

<prompt>Please speak the new time</prompt>

<catch event="nomatch noinput">

I'm sorry. I didn't get that.





<assign name="curTime" expr="newTime$.utterance"/>


the new time is 

<value expr="curTime"/>



<goto next="#mainMenu"/>



Here is the error:

[03/23/10 11:00:45] ERROR loading http://grammar.svc.tellme.com/time/time-dtmf.grxml [Load time: 29 ms; HTTP code: 404]
[03/23/10 11:00:45] ERROR "file load failed with http error code 404" at <field> tag (line 30, column 2 in file <studio resource>)


Recently for a customer we migrated Oracle database to SQL Server 2008 using SSMA tool (SQL Server Migration Assistance for Oracle).The SSMA tool also creates a SYSDB database on the SQL Server instance which contains the replica of Oracle system function that do not exists in SQL Server.


Now the scenario is following statements cannot be executed inside the user defined function of SQL Server:

1) Raising errors or exceptions handling

2) Print statements

3) DML operations  like UPDATE, INSERT, DELETE that modify tables, except for local table variables

4) A call of a stored procedure


So when SSMA tool migrated such functions from Oracle to SQL Server it created a function with additional procedure and the function then calls that stored procedure using an extended stored procedure xp_ora2ms_exec2_ex which was created by SSMA tool. The extended stored procedure xp_ora2ms_exec2_ex also internally references SYSDB database.


Client want to remove the dependency of the extended stored procedure and thereby usage of SYSDB database. Can anyone suggest an alternate way to execute the above statements inside the function?



Hi all,

I have a datetime column in a table and I want to get only time and then store that time to one temp variable and add 30 minute to that time.


Declare @StartTime varchar(50),@EndTime Varchar(50)

Set @StartTime=''

Set @EndTime=''


@EndTime = @StartTime + 30 minute
Any idea how to do this?


I have a field Birthday date . Requirement is to save a date 24 months from birthdate as soon as the birthdate is saved. I have to do it in Javascript OnLoad..

eg:-Birthdate = '24th Jan 2010' then Registration Date = ' 24th Jan 2012'




//Convert Unix epoch time to .NET DateTime

privateDateTime ConvertUnixEpochTime(long milliseconds)


DateTime Fecha = newDateTime(1970,1,1,0,0,0,DateTimeKind.Utc);

return Fecha.ToLocalTime().AddMilliseconds(milliseconds);





Can I display time info (hh:mm:ss) in a datetime field?
I saw the info is actually stored as datetime (and available as is in a subscription view). But a datetime attribute force me to one of the listed datetime formats (no time pattern found). So how can I display it correctly?


Hi folks,

How are the DateTime Field Managed Property maintained in Search Database? Does it change to UTC standard or GMT time when Selecting the property using SQL query? If it is a specific time zone, can we convert the same at backend (Sharepoint Search Database) while firing a FullTextSqlQuery like,

"Select Id from Scope() where scope='our_scope' and PublicationDate < '" +DateTime.Now "'"



I have a database table with over ten million records.  Each record is keyed by a code, a date field, and a time field.

For some queries, it is more convenient to have the date and time fields combined, but generally they need to be separate to reduce query complexity.

However, in terms of performance for these particular queries that combine these fields, is it better to add another DATETIME field instead of having a "CAST(DateField AS DATETIME) + TimeField" in the query?  If so, should this datetime field be indexed?  This table is used for both historical and transactional purposes (no updates are performed, just inserts).


Hope this make sense.


I'm working on a Crystal report that contains 4 subreports.  Each subreport passes 2 date parameters to a stored procedure.  This works, but only if a value for time is also entered in the Crystal parameter prompt.

How can I fix the stored procedure so these parameters will accept only a date as input?


I have a dataset containing multiple tables. I copy required table into a temp datatable that contain a date column. The data in entire datatable is string. The date column has the date formatted as "mm/dd/yyyy" without any time part. I have to add this datatable in output dataset and eventually write it to XML. My problem is that when I convert the string column to datetime and add rows, the time portion is appended automatically. What I did so far :

1. Clone datatable
2. Changed the DataType to DateTime
3. Copy data into cloned datatable using ImportRow (adds timepart 12:00:00 to the date)
4. Sort desired column using DefaultView.Sort
5. Copy the data to another datatable
6. Add the datatable to Dataset
7. Write to XML

The output is sorted but I see timestamp appended to the date column automatically. I don't want the time part. I only want the date part (mm/dd/yyyy) as it is in original data.


using (DataTable dtTemp = ds.Tables["Doc"].Copy())
  using (DataTable dt = dtTemp.Clone())
    dt.Columns["mailDate"].DataType = typeof(DateTime);
    foreach(DataRow dr in dtTemp.Rows)
    //Sort the data based on required columns
    dt.DefaultView.Sort = "acct, mailDate asc";
    dtDoc = dt.DefaultView.ToTable();
dtDoc is then added to output dataset. Appreciate if you can let me know how to format the date column to mm/dd/yyyy format.

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.


I'm using below code to convert local time to a particular time zone but every time the 3rd line of below code is executed i.e. convertimetoUTC then a error is thrown that kind is not specified..

I have tried to call this method with both datetime.now and datetime.utcnow but result is same. Pls help..

    MyDateTime = DateTime.SpecifyKind(MyDateTime, DateTimeKind.Utc);
      TimeZoneInfo JOTZ = TimeZoneInfo.FindSystemTimeZoneById("Tokyo Standard Time");//Jordanreturn TimeZoneInfo.ConvertTimeToUtc(MyDateTime, JOTZ); 


I am using Membership and note that all the dates set in the aspnet_Membership table are minus one hour.

My development platform is XP and my timezone is set to GMT + 0.00 with "automatically adjust clock for daylight saving changes" checked.

Currently we are in British Summer Time (joke!) which is GMT + 1 hour = BST.

If I set a DateTime field = NOW(), the DateTime field is set to BST.

When using the Login control, however, the DateTime set in any field, LastActivityDate, LastLockedDate or FailedPasswordAttemptWindowStart, is BST - 1 = GMT.

First question is, should I set dates to be in GMT all the time or BST when it applies?

If GMT all the time (which seems to be the preferred option?), how do I remove the daylight saving time when I set a DateTime field?

If BST when it applies, how do I add this to the dates set by the Login control?

Your help would be much appreciated (any codes snippets preferably in VB, please).

Thanking you in anticipation.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure