retrieve date from database as another format

I'm using oracle as my database, I have a taskdate column as TIMESTAMP. the format it save in database is 5/29/2015 10:27:04.000000 AM. can I convert it into 2015-05-29 this format when I retrieve it out??

-------------Problems Reply------------

Of course, you can. You may either use "cast .. as date" to convert value to date, and then trunc it (in case you want to leave format as date) or use to_char (if want value as character). Final expressions for both options may be something like that:

SELECT TRUNC(CAST(column AS DATE)) FROM your_table...

or

SELECT TO_CHAR(column, 'yyyy-mm-dd') FROM your_table...

the format it save in database is 5/29/2015 10:27:04.000000 AM

Date/Timestamps doesn't have any format. Oracle doesn't store the date/timestamps in the format you see. The format you see is only for display purpose. Date is internally stored in 7 bytes which is Oracle's proprietary format.

can I convert it into 2015-05-29 this format when I retrieve it out??

For display:

You need to use TO_CHAR along with desired format model to display in your desired format.

For example,

SQL> SELECT SYSTIMESTAMP, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD') my_tmstmp FROM DUAL;

SYSTIMESTAMP MY_TMSTMP
---------------------------------------- ----------
29-MAY-15 11.40.25.054000 AM +05:30 2015-05-29

SQL>

For date-arithmetic:

For date arithmetic you should leave the data type as date itself. You could use TRUNC to truncate the time portion and leaving only the date portion:

For example,

SQL> alter session set nls_date_format='YYYY-MM-DD';

Session altered.

SQL> SELECT SYSTIMESTAMP, TRUNC(SYSTIMESTAMP) my_tmstmp FROM DUAL;

SYSTIMESTAMP MY_TMSTMP
---------------------------------------- ----------
29-MAY-15 11.42.41.255000 AM +05:30 2015-05-29

SQL>

Category:asp.net Views:3 Time:2018-09-02

Related post

  • Retrieve data from database every few seconds? 2010-05-12

    i need to retrieve data from database every few seconds, display them. i use C#. When i press one button, the data should be retrieved every few seconds,the data is then displayed in a form, any idea how to do it? --------------Solutions-------------

  • .net: Do you prefer populating a datatable when retrieving data from database or populating a generic list of your own custom object? 2011-01-31

    which senario do you prefer when you're retrieving data from database? 1 - Populating a datatable and then bind a datagridview to it? OR 2- populating a genric list of your own custom object and then bind a datagridview to it? Thank you -------------

  • java.lang.NullPointerException - Error in retrieving data from database with jsf 2.0 and spring jdbc 2011-08-11

    I have a web project creating with JSF 2.0 integrate with Spring JDBC using IDE Netbeans 7.0. The problem is when i wanna retrieve data from database to fill in JSF Component (DataTable) occur an error like in the following.. This is my snipset code

  • How to retrieve data from database in android Creating database on SqliteBrowser 2011-10-17

    I have jokes.db database. This database file imported from using this article., http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/. Now i have Jokes.db in to /data/data/a.b.c/database/Jokes.db. In this database ,

  • Not able to retrieve data from database in ios 2012-01-12

    I want to save some data into sqlite database and the read it, there is no problem while saving the data but i am not able to retrieve data from database. Here is the code that I'm using. //>>>>>> to save in sqlite Database -(void)s

  • Best practice when storing/retrieving data from database inside class 2012-02-26

    I would like to know what's the best code design when storing and retrieving data from a database when working with objects and classes. I could do this in two ways. In the class constructur I query the database and stores all info in instance variab

  • How to insert and retrieve data from database with Web Service in java using JAX - RS and tomcat in eclipse 2012-04-10

    Possible Duplicate: How to insert data from database with Web Service in java using JAX - RS How to insert and retrieve data from database using JAX - RS(Jersey) and tomcat in java using eclipse? Please give the solution. --------------Solutions-----

  • retrieving data from database and printing in a xml format 2011-11-02

    I want to retrieve the group of data from database and print it in list view in a xml format.so that i can use that xml format data in a android application. I am able to retrieve one value from the database and print it in xml format but i am not ab

  • PHP OOP: retrieve data from database 2011-07-24

    I'm pretty new to OOP (and also in PHP programming), and I have some troubles using objects and interfacing them with db data (MySql in my case, but this is irrelevant). I have on the DB a table USERS. I declared a class User in the PHP code, with th

  • How to retrieve data from database using webservices (JAX - RS) in eclipse using Java 2012-04-10

    I have done inserting a record into database but I don't know how to retrieve it. My code is: Account.java: package com.fetch; import java.io.Serializable; import javax.persistence.Column; import javax.persistence.Id; public class Account implements

  • Passing value between form and retrieving data from database 2013-02-22

    In my windows form application, I have one form which user does input such as name address...blah.. When user press add entry, all the input entry will be added to the database and at the same time, order number will be passed to the another form whe

  • call php function that is in a different file from a javascript function to retrieve data from database 2010-11-01

    How do you call a PHP function that is in a different file from a JavaScript function? I have a JavaScript function that recieves a variable from an onClick function. The variable is an id that I will use to retrieve data from MySQL database. The var

  • retrieve data from database and implement it in table view 2011-06-22

    I am new in iphone programming. I am working for small iphone application in which I have put three textfield for name, contact, email id and saving all this data in database. Now I want to retrieve the data from database and display it in table-view

  • ASP.NET ListView Control: how to style it and retrieve data from database in specific columns 2011-10-16

    I am trying to use the ListView control to show a list of data from the database as following: Employee Name: xxxxxxxxx ID: 111111 Job Title: yyyyyxxxx Organization: uuuuuu which means I want to list the data in a table of 4 columns that orders as fo

  • Retrieving data from database 2012-01-01

    I am trying to retrieve data from a database and I am using code that I found on blogs with some changes. This is my php file: <?php mysql_connect("host","username","password"); mysql_select_db("peopledata"); $q=mysql_query("SELECT * FROM people W

  • Java: Retrieving data from Database and load in JTable 2012-01-06

    I know how to retrieve data (just text in a table) from a database in Java and how to show it in the console. But I want to load it in a JTable. Is there a good (modern) way (tutorial), without using Vectors? --------------Solutions------------- Chec

  • Retrieve data from database into a html form 2012-02-28

    I have code to retrieve data from a database into a form but it doesnt seem to be working. The code below is my attempt but it doesnt work. Currently, when I click the submit button 'retrieve rose' it does nothing... //if we have no errors, do the SQ

  • Retrieve data from database 2010-05-11

    I need to use C# to get data from database(sql) and the data in database is updated every few seconds. So do I have to make a loop or there is a better way to do that? Is it possible that when database is updating, the program can wait until it finis

  • problem in php when retrieving data from database 2011-09-16

    I have one problem regarding retriving data from database. I have created one database in userrecord and its table name is tbl_user. I am trying to display the user information when they log in the system. i.e view logged user profile. The code is as

  • retrieve data from database which contain more than one data 2011-12-07

    I want to retrieve data from sqlite. user can have more than 1 score and i want to display it. it can show if user just have only one score, but it shows blank screen if user have more than one score. These are my code ScoreDetailActivity.java public

  • java - retrieving data from database and display using JcomboBox 2012-02-17

    I would like to be able to select a data from database and display it using the comboBox. I have the following code but it does not display the data in the comboBox. I do realize there is codes missing to display the data and my SQL statement is not

  • can't copy data from database to another? MYSQL 2018-04-09

    Please I can't copy data from database A to B I have same tables in A & B and same columns too. I want to copy Data from A.profile to B.profile This is my code : ( same pass,user, localhost) mysql_connect($host, $user, $pass)or die("cannot connec

  • how to retrieve data from database in sqlite database ina android 2011-11-29

    I have mainactivity in which i have button of "insertrecipe". when user clicks button another activity will start using intent and will go from "mainactivity" to "insertrecipe" and user will be shown a text field in which he can type text and also a

  • Zend Framework: right way to retrieve data from database 2009-05-10

    I am working on a project with zend framework and i need your advise on the right way to fetch data from the database. Am making use of Zend_Layout to load my template. The appropriate view is then loaded into the template. On the template, there is

  • how to connect and insert and retrieve data from database? 2009-07-14

    $con = mysql_connect("localhost","music123_sri","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("music123_telugu", $con); $sql="INSERT INTO xml (File) VALUES (" . mysql_escape_string($xmlString) . ")"; $data =

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.197 (s). 11 q(s)