Jump to content
JasperReports Library 7.0 is now available ×

Date extraction on string date type


MikeM19
Go to solution Solved by Tom C,

Recommended Posts

Hello guys,

I'm having a problem, i'm trying to design a report in Ireports, i need to extract the invoice date information from our DB, then run the report based on a date range i mean by example extract information from August 1th to August 20th, the problem i have is that in our DB the date field is a string not a date format, all the date have the format "2015-06-22T22:09:44" is there any way i can resolve this? i hope any of you guys can help me here, the language i'm using is SQL.

Regards!

Link to comment
Share on other sites

  • Replies 6
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Hello Jlongoria,


Well i tried what you kindly suggested but it didn't work i'm getting the following error: 'DATE' is not a recognized built-in function name

I'm sorry but my knowledge on SQL is pretty basic so i'm trying to learn as much as possible, the  sentence i'm using to extract the date information string is:

Select invoices."invoice_date" AS invoices_invoice_date

From my_table

thats the sentence the Ireport's query designer gave me when i was creating the template using the wizard, but as i said i cannot convert the string to a date type, any additional idea?

Regards!
 

Link to comment
Share on other sites

Which data base are you using as your report data source? Different DB has different functions to convert String into Date. For example, you can test the following mock up query in the corresponding database to see how it works:

- PostgreSQL:

SELECT to_date('2015-06-22T22:09:44','YYYY-MM-DD') as converted_date

- MySQL:

SELECT STR_TO_DATE('2015-06-22T22:09:44','%Y-%m-%d') as converted_date

Link to comment
Share on other sites

Thanks for your response tchen, i'm using MySQL as data source, my question here is if i'm not trying to convert just an specific date like 2015-06-22T22:09:44, i'm trying to extract a bunch of date values from a field this query should work? and if it does which must be the sintax for this? sorry for the simplicity of the question but as i said i'm pretty new with SQL.

Here is the whole scenario of what i'm trying here, i have a table with some attributes about invoices (date, provider, city, etc) i can extract the information with a simple query but what i need is to select a data range so i can extract all the registers within that range but the problem is with the invoice date field because in the DB is a string type, so in a few words i have to convert the string field to a date field and then compare with a couple of parameters for the date range.

i hope i was clear enough so you guys can help me here.

Regards!

Link to comment
Share on other sites

  • Solution

Please try:

SELECT STR_TO_DATE(invoices.invoice_date,'%Y-%m-%d') as invoices_invoice_date from your_table

This will convert date string in your String type column "invoice_date" into a Date type data in the query result set. You can then use a date range comparison in your SQL query to select records based on invoices_invoice_date values.

Please refer to MySQL reference material for SQL coding information. The following MySQL posting might be useful:

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Link to comment
Share on other sites

Hello tchen,

 

Thanks for you reply, now i can say that you put me on the right direction because finally is working fine, i dont want to be annoying but there is any equivalent function on SQL server because i want to apply the same actions but with a different data source? thanks again!

Regards!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...