• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Get only date in a select statement

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i think its an alternate Roberto.
 
Naadir Peterson
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanx 4 the help...

really appreciate it..

 
Roberto Spier
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 .
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic