displaying date and time - 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

General SQL Server Forums

New to SQL Server Programming

displaying date and time

Author

Topic

krystledolan
Starting Member

2 Posts

Posted - 2011-08-17 : 14:08:36

SQL> desc guide;
Name Null? Type
----------------------------------------- -------- ----------
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATE

select channel.channel_id AS "Channel", supplier_name AS "Supplier", program_name AS "Program", package_name,
program.rating_code AS "Rating", To_DATE(show_datetime, 'DD-MON-YYYY hh12:mi AM') as "Show Time"
from channel, supplier, program, package, rating, guide
where channel.channel_id = program.channel_id
and package.package_id = channel.package_id
and rating.rating_code = program.rating_code
and supplier.channel_id = channel.channel_id
and channel.channel_id = guide.channel_id
and guide.show_datetime BETWEEN TO_DATE('15-aug-2011') AND ('16-AUG-2011')
order by channel.channel_id, guide.show_datetime;

Here is the display:

Channel Supplier Program PACKAGE_NAME Rating Show Time
---------- ------------------------------ -------------------------------------------------- -------------------- ------- ---------
1 NBC C-3PO RETURNS Movies_Galore PG 15-AUG-11
1 NBC BARETTA Movies_Galore M 15-AUG-11
1 NBC ALASKAN WILD YONDER Movies_Galore PG 15-AUG-11
1 NBC MANIC MONDAYS Movies_Galore R 15-AUG-11
2 NBC WOODLAND FAIRIES Total_Watcher G 15-AUG-11
2 NBC AMONG THE LAVENDAR FIELDS Total_Watcher MA 15-AUG-11
2 NBC TOKYO HEIGHTS Total_Watcher M 15-AUG-11
3 ABC TOP GLOBAL NEWS News_Globe G 15-AUG-11
3 ABC US BUSINESS WORLD News_Globe G 15-AUG-11
4 ABC CBS NEWS Total_Watcher G 15-AUG-11
4 ABC ABC NEWS Total_Watcher G 15-AUG-11

Channel Supplier Program PACKAGE_NAME Rating Show Time
---------- ------------------------------ -------------------------------------------------- -------------------- ------- ---------
4 ABC NBC TONIGHT Total_Watcher G 15-AUG-11
5 FOX HOME MAKEOVER Couch_Potato G 15-AUG-11
5 FOX WWW.ONLINEBARGAINS.COM Couch_Potato G 15-AUG-11
5 FOX COOKING WITH MARIO Couch_Potato G 15-AUG-11
6 FOX FITNESS WAVE Movies_Galore G 15-AUG-11
6 FOX ALL THAT GLITTERS Movies_Galore G 15-AUG-11
6 FOX HOUSE OF OHM Movies_Galore G 15-AUG-11
6 FOX SHOPPING NETWORK Movies_Galore G 15-AUG-11
7 NBC GREATEST WORLD HISTORY Total_Watcher G 15-AUG-11
7 NBC TRAVELING EUROPE Total_Watcher G 15-AUG-11
7 NBC THE NATURAL WORLD Total_Watcher G 15-AUG-11

Channel Supplier Program PACKAGE_NAME Rating Show Time
---------- ------------------------------ -------------------------------------------------- -------------------- ------- ---------
10 WARNER HOOPS AND HOLLERS Total_Watcher G 15-AUG-11

23 rows selected.

when I run this query it only displays the date but no time. So can anyone help me out on how to display it with
the date and time? Thanks in advance for all help.

krystle

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-17 : 14:13:59

Try changing
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATE

To:
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATETIME

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-17 : 14:16:02

It may have to do with the behavior of the To_DATE function. Isn't To_DATE an Oracle function? This forum is for Microsoft SQL Server, so not many people may be familiar with Oracle functions. You may get better responses at a general database forum or Oracle forums; dbforums.com, for example.

krystledolan
Starting Member

2 Posts

Posted - 2011-08-17 : 14:23:02

I apoligize I will try to find an oracle sql forum I am so new to sql but thank you for all the help so far I am going to try and change data type to datetime

krystle

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-17 : 14:35:28

Krystle,

Good luck with it! Try www.developerbarn.com and www.dbforums.com for Oracle forums. I really like the first one a lot, not many people, but very helpful folks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources