date format from yyyy-mm-dd to dd-mm-yyyy

Dear visitor, welcome to Jabaco - Community. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Thursday, October 4th 2012, 10:14am

Hello everybody!

I need to use the format function to convert me date results from yyyy-mm-dd to dd-mm-yyyy BUT....

i use a function like this

Public Function Format(d As Date, fmt As String) As String
Dim df As New java#text#SimpleDateFormat(fmt)
Format = df.format(d)
End Function

then call her like this....

Set Data = Order.executeQuery("SELECT imerominia FROM esoda_exoda order by imerominia")
Do While Data.next()
imerominia = Data.getString("imerominia")
imerominia = Format(imerominia, "dd-mm-yyyy")
java#lang#system.out.println(imerominia)
Loop

I have these dates in my db (records)
2008-09-17
2008-09-18
2008-09-19
and my results are
17-00-2008
18-00-2008
19-00-2008

i mean that after format i always have 00 in mm

Does anybody know why?

Go to the top of the page

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

Thursday, October 4th 2012, 1:25pm

RE: date format from yyyy-mm-dd to dd-mm-yyyy

Quoted from "fomaxrge"

imerominia = Format(imerominia, "dd-mm-yyyy")

The date ist case sensitive.
You have to write
imerominia = Format(imerominia, "dd-MM-yyyy")

Greatings
theuserbl

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Thursday, October 4th 2012, 1:51pm

Hey there,

...or you could let your databaseengine do the work for you:

	MySQL queries
1	Set Data = Order.executeQuery("SELECT DATE_FORMAT(imerominia, '%d-%m-%Y') FROM esoda_exoda order by imerominia")

You may have to check the right syntax for the format string for Mysql!

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Thursday, October 4th 2012, 1:54pm

See also http://dev.mysql.com/doc/refman/5.5/en/d…ion_date-format

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Thursday, October 4th 2012, 3:04pm

Quoted from "theuserbl"

imerominia = Format(imerominia, "dd-MM-yyyy")

in this case returns me always ...

04-04-0010
10-04-0004
04-04-0010
10-04-0004
04-04-0010
10-04-0004

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Thursday, October 4th 2012, 3:08pm

Quoted from "Dani"

Set Data = Order.executeQuery("SELECT DATE_FORMAT(imerominia, '%d-%m-%Y') FROM esoda_exoda order by imerominia")

when i make SELECT DATE_FORMAT(imerominia, '%d-%m-%Y') FROM esoda_exoda order by imerominia into my mysql editor everything is fine but when i return to jabaco and make...

Set Data = Order.executeQuery("SELECT DATE_FORMAT(imerominia, '%d-%m-%y') FROM esoda_exoda order by imerominia")

Do While Data.next()

imerominia = Data.getString("imerominia")
java#lang#system.out.println(imerominia)

Loop

it returns me this error
java.sql.SQLException: Column 'imerominia' not found.

This post has been edited 1 times, last edit by "fomaxrge" (Oct 4th 2012, 3:34pm)

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Thursday, October 4th 2012, 5:17pm

Hey there,

I guess it expects an alias, try this:

	MySQL queries
1	SELECT DATE_FORMAT(imerominia, '%d-%m-%Y') AS imerominia FROM esoda_exoda ORDER BY imerominia;

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

Friday, October 5th 2012, 3:13am

Quoted from "fomaxrge"

Quoted from "theuserbl"

imerominia = Format(imerominia, "dd-MM-yyyy")

in this case returns me always ...

04-04-0010
10-04-0004
04-04-0010
10-04-0004
04-04-0010
10-04-0004

Don't know, why it don't work for you.

Here it works fine:

Jabaco Source

Public Function Format(d As Date, fmt As String) As String
  Dim df As New java#text#SimpleDateFormat(fmt)
  Format = df.format(d)
End Function

Public Sub main(ByJava args() As String)
   Dim imerominia(3) As String
   imerominia(1) = "2008-09-17"
   imerominia(2) = "2008-09-18"
   imerominia(3) = "2008-09-19"
   
   Dim i As Integer
   For i=1 To 3
     System.out.println(Format(imerominia(i), "dd-MM-yyyy"))
   Next i
End Sub

Output:

	Source code
1 2 3	17-09-2008 18-09-2008 19-09-2008

Or without array:

Jabaco Source

Public Function Format(d As Date, fmt As String) As String
  Dim df As New java#text#SimpleDateFormat(fmt)
  Format = df.format(d)
End Function

Public Sub main(ByJava args() As String)
   Dim imerominia As String
   imerominia = "2012-04-11"
   System.out.println(Format(imerominia, "dd-MM-yyyy"))
End Sub

Output:

	Source code
1	11-04-2012

Greatings
theuserbl

Go to the top of the page

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

Friday, October 5th 2012, 3:31am

Quoted from "fomaxrge"

in this case returns me always ...

04-04-0010
10-04-0004
04-04-0010
10-04-0004
04-04-0010
10-04-0004

Looks like that an output goes again to the input.

If you switch in
04-04-0010
the day and the year you have
10-04-0004
and if you switch the two again, you have again
04-04-0010

And I have tried it out. An

Jabaco Source

Public Function Format(d As Date, fmt As String) As String
  Dim df As New java#text#SimpleDateFormat(fmt)
  Format = df.format(d)
End Function

Public Sub main(ByJava args() As String)
   Dim imerominia As String
   imerominia = "04-04-0010"
   System.out.println(Format(imerominia, "dd-MM-yyyy"))
End Sub

gives out:

Quoted

10-04-0004

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Friday, October 5th 2012, 10:48am

first of all thank you very much for your precious time you spend on my query.

The above dates
04-04-0010

10-04-0004
are not included in my table.

Also i want to format my imerominia variable that is used a mysql query in a loop
for example....

Do while Data.next()
...
imerominia=Data.getString("imerominia")
imerominia = Format(imerominia, "dd-MM-yyyy")
...
Loop

also i was trying today to make something like this ...
dateNow = Format(NOW, "dd,MM,yyyy") and returns me also a very strange date.
05-07-10????

Go to the top of the page

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

Friday, October 5th 2012, 12:07pm

Quoted from "fomaxrge"

The above dates
04-04-0010

10-04-0004
are not included in my table.

I know. But
2010-04-04
or
2004-04-10
is in your table.

And if for example
2010-04-04
is in your table, it comes after the formatting out as
04-04-2010
and when it goes again to the input, it comes out as
10-04-0004
and when it goes again to the input, it comes out as
04-04-0010
...

Quoted

Also i want to format my imerominia variable that is used a mysql query in a loop
for example....

Do while Data.next()
...
imerominia=Data.getString("imerominia")
imerominia = Format(imerominia, "dd-MM-yyyy")
...
Loop

also i was trying today to make something like this ...
dateNow = Format(NOW, "dd,MM,yyyy") and returns me also a very strange date.
05-07-10????

Then try to debug it. I don't know how to better describe it.

For example: give the variable for the formatted date an other name:

	Jabaco Source
1 2 3 4 5 6	Do while Data.next() ... imerominia=Data.getString("imerominia") imerominia2 = Format(imerominia, "dd-MM-yyyy") ... Loop

so you can avoid, that an output goes again to an input.

look what happens after every step, by including text-outputs for this:

Jabaco Source

Do while Data.next()
...
  imerominia=Data.getString("imerominia")
  System.out.println("imerominia: " + imerominia);
  imerominia2 = Format(imerominia, "dd-MM-yyyy")
  System.out.println("imerominia2: " + imerominia2);
...
Loop

Look from the output, that every step is ok.

Greatings
theuserbl

Go to the top of the page

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

Friday, October 5th 2012, 12:42pm

Hey there,

I am with theuserbl here, it does look like some permutation!

Since you know the input format you could do some stringshuffeling:

	Jabaco Source
1 2 3 4 5	Public Function MyFormat(d As String) As String Dim sd() As String sd = Split(d,"-") MyFormat = sd(2) & "-" & sd(1) & "-" & sd(0) End Function

Dani

latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Sunday, October 7th 2012, 9:07pm

Finally i did f something like this...

1>
to_date.Text = Day(dd) &"-"& Month(mm) &"-"& Year(yyyy)

2>
Set Data = Order.executeQuery("SELECT min(imerominia) FROM esoda_exoda")
Do While Data.next()
fromDate = Data.getString("min(imerominia)")
from_date.Text = Format(fromDate, "dd-MM-yyyy")
Loop

***the funny is, that all the dates i had were...***

2012-08-06
2012-09-13
2012-09-17
2012-09-19
2012-09-21
2012-09-25
2012-09-26
2012-09-27

there is nowhere

2010-04-04

or

2004-04-10

in my table.

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Sunday, October 7th 2012, 9:09pm

theuserbl and Dani thank you very much for your help.
You helped me a lot.

Go to the top of the page

fomaxrge

Trainee

Posts: 88

Date of registration: Aug 14th 2012

Tuesday, October 9th 2012, 5:18pm

hi again!
Finally my problem did not solved.
Can you please try this ...

MsgBox Format("06-08-2012", "yyyy-MM-dd") to tell me what is coming back to you?

to me it returns....0012-02-02????

Why ?

This post has been edited 1 times, last edit by "fomaxrge" (Oct 9th 2012, 5:39pm)

Go to the top of the page

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

Tuesday, October 9th 2012, 6:23pm

Quoted from "fomaxrge"

hi again!
Finally my problem did not solved.
Can you please try this ...

MsgBox Format("06-08-2012", "yyyy-MM-dd") to tell me what is coming back to you?

to me it returns....0012-02-02????

Same here.

Quoted

Why ?

Before you used your Format-function to convert a yyyy-MM-dd date to a dd-MM-yyyy date.
Now you are trying with the same function the other way around. And all we see is, that your function don't accept any dd-MM-yyyy date-format as input.

But for this Dani have written a MyFormat function, which permute the first number and the last number.
And
MsgBox MyFormat("06-08-2012")
gives out
2012-08-06

Greatings
theuserbl