You are not logged in.

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

1

Thursday, October 4th 2012, 10:14am

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

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?

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

2

Thursday, October 4th 2012, 1:25pm

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

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


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

Greatings
theuserbl

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

3

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

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

5

Thursday, October 4th 2012, 3:04pm

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

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

6

Thursday, October 4th 2012, 3:08pm

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)


Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

7

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

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

8

Friday, October 5th 2012, 3:13am

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
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

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

9

Friday, October 5th 2012, 3:31am

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

1
2
3
4
5
6
7
8
9
10
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

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

10

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????

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

11

Friday, October 5th 2012, 12:07pm

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

1
2
3
4
5
6
7
8
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

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

12

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

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

13

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.

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

14

Sunday, October 7th 2012, 9:09pm

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

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

15

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)


theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

16

Tuesday, October 9th 2012, 6:23pm

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

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

17

Tuesday, October 9th 2012, 6:31pm

YESSS it is working thank you thank you thank you! :)

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

18

Tuesday, October 9th 2012, 6:34pm

how can i use the Dani's function to do the opposite?
2012-08-06 to 06-08-2012?

theuserbl

Intermediate

Posts: 436

Date of registration: Dec 20th 2008

  • Send private message

19

Tuesday, October 9th 2012, 6:37pm

how can i use the Dani's function to do the opposite?
2012-08-06 to 06-08-2012?
The same way:
MsgBox MyFormat("2012-08-06")

Greatings
theuserbl

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

20

Tuesday, October 9th 2012, 6:41pm

so simple?
you are perfect.
both of them!
thank you

now back to work!

Rate this thread
WoltLab Burning Board