aspose file tools*
The moose likes JDBC and the fly likes Get only date in a select statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Get only date in a select statement" Watch "Get only date in a select statement" New topic
Author

Get only date in a select statement

Naadir Peterson
Ranch Hand

Joined: Sep 22, 2004
Posts: 37
Hi

How do i return only the date without the time when selecting a datetime variable in sql2000. and how do i return only 2($253.23) values after the decimal when selecting a money variable.

thanx.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
you had better, do it in your code. assuming you know how to do.
Naadir Peterson
Ranch Hand

Joined: Sep 22, 2004
Posts: 37
but i need to select records of a certain date.. if i select only the date without the time it returns nothing...
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
here is something that might work for you with SQL Server.

'041231' = (cast floor(cast <date-time-column> as float) as varchar)

try it out. dont know SQL Server though, oracle guy.
Roberto Spier
Greenhorn

Joined: Sep 13, 2004
Posts: 7
Hi,

Originally posted by Naadir Peterson:
but i need to select records of a certain date.. if i select only the date without the time it returns nothing...


to retrieve one date records through a dateTime column, you should use BETWEEN clause:

(Examples using ISO format)

SELECT *
FROM table
WHERE the_date between '2004-10-09T00:00:00' and '2004-10-09T23:59:59'; (using ISO format)

or just

WHERE the_date between '2004-10-09' and '2004-10-10'

This will return all records from 10/9/04 00:00:00 thru 10/9/04 23:59:59 or thru 10/10/10 00:00:00. Both will almost be fine.

bob
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
i think its an alternate Roberto.
Naadir Peterson
Ranch Hand

Joined: Sep 22, 2004
Posts: 37
Thanx 4 the help...

really appreciate it..

Roberto Spier
Greenhorn

Joined: Sep 13, 2004
Posts: 7
i think its an alternate Roberto.


Yes, but doing comparisons with database native types would'nt be faster than with such conversions?

BTW, how would this work with other dbms than SQL Server?
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
yeah, its particularly for SQL Server. for other we would go for something else or may be something you have mentioned.

roberto solution sounds better in this term. but again its an alternative .
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why not use a PreparedStatement and bind a Date Object? There's no formatting issues with that approach.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by Paul Sturrock:
Why not use a PreparedStatement and bind a Date Object? There's no formatting issues with that approach.


yeah quite right. i said the same thing before that you should do it in your code. but dont know why it was not working with Naadir.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Get only date in a select statement