How to fetch Date from DateTime in mySql

[Raza Mohd]

hi all,

i am having a database with Column(Transaction date) which is of DateTime.
How can i fetch the records with only Date criteria.

I need to get the records on daily basis.
where "Transaction Date" is 2010-06-23 rather than time stamp appended with it.

Or is there any other option to select records on date.

Thanks in advance.

Raza!

[Martin Vanyavchich]

I would need more info to answer you propperly.

I presume you fetch some records with an SQL statement into a ResultSet object. Then you fetch data on a row by row basis, geting Date with getDate function. Date has hours and minutes, you can manualy reset them to 00:00. If you only need a formated String to print out somewhere, use SimpleDateFormat

Something like:

Date date = new Date();
SimpleDateFormat dateFormater = new SimpleDateFormat("dd.MM.yyyy");
String formatedDate = dateFormater.format(date);
System.out.println(formatedDate);

[Raza Mohd]

I need to convert new date() object to Date object.(yyyy-MM-dd).
can you tell me how to do that.

First i convert the Date object to String ("yyyy-MM-dd")
But how to parse it to Date.

i hv got a piece of code.

public Date convertToDate(Date now)
	{	
                Date today = null;
		formatter = new SimpleDateFormat("yyyy-MM-dd");
		try {
			today =formatter.parse(now.toString());
			System.out.println("todays date is" + today);
		} catch (Exception e) {
			// TODO: handle exception
		
		}
		return today;
	}

[Martin Vanyavchich]

Raza Mohd, you can't format the Date object. You can only get a formatted string object, using a formatter and a date object. (unless you want to extend Date and override its toString method ... don't do it )

here's what I think you want:

  public static void main(String[] args) {
    Date today = new Date(); // this makes and instance of Date of 'now'
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    try {
      String todayFormatted = formatter.format(today); // make a formated string from our 'now' date
      System.out.println("todays date is " + todayFormatted); // print it out :)
    } catch (Exception e) {
      // TODO: handle exception
    }
    
    // badly readable onliner that does the same ...
    System.out.println("todays date is " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
  }

Why would you use this? If you need to query for todays dates there is a current time function in mySQL CURDATE() and for current time there is CURTIME()

[Raza Mohd]

Yes , I wants that.


I need to get Date As input from the user and corresponding to that date, want to search the records.
So what ever Date user inputs, depending upon that Qury will retrive the data.

So How can it be done?

thanks

[Martin Vanyavchich]

This should do it

  public static void main(String[] args) {
    String usersInput = "2010-3-1";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    Date aDay = null;
    String aDayFormatted = null;
    try {
      /*
       * We try to parse the users date into a Date object. You will use this
       * object (aDay) in your query.
       */
      aDay = formatter.parse(usersInput);
      System.out.println("parsed date is " + aDay);

      // if you wish to have a formated output somewhere, use this String
      aDayFormatted = formatter.format(aDay); 
      System.out.println("formatted date is " + aDayFormatted);
    } catch (ParseException parseEx) {
      // TODO: if user inputed date is in wrong format an exception will be thorown, handle it!
    }
  }