File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Get only date in a select statement

 
Naadir Peterson
Ranch Hand
Posts: 37
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you had better, do it in your code. assuming you know how to do.
 
Naadir Peterson
Ranch Hand
Posts: 37
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i think its an alternate Roberto.
 
Naadir Peterson
Ranch Hand
Posts: 37
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanx 4 the help...

really appreciate it..

 
Roberto Spier
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not use a PreparedStatement and bind a Date Object? There's no formatting issues with that approach.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic