This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Date Format

 
rani bedi
Ranch Hand
Posts: 358
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In case I have do a lot of query on dates being inputted by user in a text box(string). In which date format should I store the date in database so as to have maximum performance?
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All the databases I am familiar with have a date data type, and its internal format is not set by you. You can set the default display format. So its already set up to be efficient as long as you don't use a function on it
where tabdate > to_date('20010501', 'YYYYMMDD') OK
where to_char(tabdate, 'YYYYMMDD') > '20010501) NOT OK
Dan
 
Andrew Shafer
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

So to answer your question, you should store the Dates as . . .drum roll. . .Dates.
It will depend on the DB, but most will have multiple acceptable formats for input and retrieving the dates. PostgreSQL has a DATESTYLE setting for example. You want to make sure your input conforms to your DB.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
java.sql.Date.toString() spits out the SQL standard date representation (see the JavaDoc). But I'd use PreparedStatement anytime; you just set your date as a Date parameter and never worry about constructing a string representation of it. Safe and simple.
- Peter
 
Vikas Aggarwal
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The best way to recieve the dates is in mm/dd/yyyy format but you can always make changes in the format while entering in to the data base.
Originally posted by parmeet bedi:
In case I have do a lot of query on dates being inputted by user in a text box(string). In which date format should I store the date in database so as to have maximum performance?


------------------
Vikas Aggarwal
Technology Associate
Shakun Global Networks
www.vikinsa.com
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic