oracle date query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

oracle date query

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-09 : 14:57:15

Hello forum:
I am selecting a date field from an oracle database, that table field is of datatype timestamp(6). which means date is store as date including miliseconds, example 20-JUN-03 04.55.14.000000 PM.

I need to cast this field to date, which i did as:
cast(event_date as date) and got a return 06/20/03 4:55 pm,

Question:
I program need to get this date as 06/20/03 no time included.
I can't find a way to remove the time from date.
i use trunc function but did not work
Thank you

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-03-09 : 15:08:38

If this is an Oracle question, look up the to_char function. If this is a SQL Server question, look up the Convert () function.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-09 : 15:09:12

In sql server, the most efficient method of truncating to a whole date value is:

dateadd(day, datediff(day, 0, [YourDate]), 0)

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-09 : 15:12:58

i am querying a oracle database

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-09 : 15:29:09

I is an oracle database server, I need to remove the time from a date datatype EX.

12/08/06 3:55:36 pm I need to remove time

so that I pass this parameter to my application as:

12/08/06

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 15:35:43

Duplicate thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63017

Tara Kizer
aka tduggan

alxtech
Yak Posting Veteran

66 Posts

Posted - 2006-03-09 : 15:37:10

DUPLICATE POST ARE NOT ALLOWED? :\ SORRY!!!!!! :(

this is my current code:

In database EVENT_DATE IS OF THE TYPE TIMESTAMP(6)

Set recordSet = dbConn.Execute("SELECT EVENT_NAME,CATEGORY,cast(EVENT_DATE AS DATE) FROM table_name WHERE cast(EVENT_DATE AS DATE) = '" & mDate & "'")

mDate has to be: MM/DD/YYYY no time

otherwise application won't work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 15:39:16

Any answer that we give will be for SQL Server and not for Oracle. So I'd suggest posting in an Oracle forum, such as the one over at dbforums.com.

Tara Kizer
aka tduggan

Tahsin
Starting Member

34 Posts

Posted - 2006-03-09 : 15:39:32

Why don't you take the 8 leftmost characters:

SQL syntax:
DECLARE @test1 as varchar(30)
SET @test1 = '06/20/03 4:55 pm'
print LEFT(@test1, 8)

Oracle Syntax:
SET SERVEROUTPUT ON
DECLARE
test1 VARCHAR(30);
BEGIN
test1 := '06/20/03 4:55 pm';
test1 := SUBSTR(test1, 1, 8);
DBMS_OUTPUT.PUT_LINE (test1);
END;

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-03-10 : 09:19:22

Try this:


Select to_char(sysdate, 'MM/DD/YYYY') from dual;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources