Date extraction on string date type

[MikeM19]

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!

[jlongoria]

I think that you should be able to do the following:

SELECT DATE( LEFT( '2015-06-22T22:09:44', 10) ) FROM YOUR_TABLE[/code]

    LEFT() gets the date part of the string, i.e. the first 10 characters, then DATE() converts it into a Date object.

 

Here is a page with lots of resources for Date/Time manipulation in SQL.

 

 

 

[MikeM19]

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!
 

[Tom C]

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

[MikeM19]

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!

[Tom C]

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

[MikeM19]

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!