Date formatting...

[GaryVass]

I am tyring to save some data to a Mysql database that uses the YYYY-DD-MM date format. I have tried to use the format command... format(TDATE, "#yyyy-mm-dd#", ".") in various configuations, but can't make it work.

The program takes information from a text file and saves it into the database, but the database will not accept date data unless it is in that format. The text file has the date as dd-mm-yyyy... I can not change the text file format, so I must reconfigure the date before saving to the database.

Any suggestions?

Thanks

[berndnoetscher]

I am tyring to save some data to a Mysql database that uses the YYYY-DD-MM date format. I have tried to use the format command... format(TDATE, "#yyyy-mm-dd#", ".") in various configuations, but can't make it work.

The program takes information from a text file and saves it into the database, but the database will not accept date data unless it is in that format. The text file has the date as dd-mm-yyyy... I can not change the text file format, so I must reconfigure the date before saving to the database.

Any suggestions?

Thanks

This works.

Code: Select all

msgbox(format(now(), "yyyy-MM-dd", "."))

[GaryVass]

Yes, it works with the now() in the equation, but this does not work: (complete code not shown, just snippets)

dim sDate as string
dim TDATE as date

input #1 sDate (inputs from text file, works if sDate is dim as string, not as date)

TDATE= datevalue(sDate)


msgbox format(TDATE, "yyyy-mm-dd", ".")

I keep getting a blank for TDATE in the msgbox. I have tried declaring sDate as Date, and TDATE as string and trying different date conversions on both, but I still get blanks when I do.

[GaryVass]

Ok, I just looked at the msgbox output for the format(now(),"yyyy-mm-dd", ".")) and noticed this:

2010-57-26

the year is right, the day is right, but we are not in month 57 yet?? Do I need to format it different, or is this a bug?

When I ran the program a second time, the month changed to 02 ?? still not 07?

------

I tried to delete the prior posts, but did not know how. I have found a solution, by using this code:

TDATE = Mid(sDate,7,4) + "-" + Mid(sDate,1,2) + "-" + Mid(sDate,4,2)

I am able to reconstruct the text input into the required format for my database.

Still, not sure why the month is not right (first part of this post), but I don't need to worry about that, at least not right now.

Thanks for the help.

[pappawinni]

Ok, I just looked at the msgbox output for the format(now(),"yyyy-MM-dd", ".")) and noticed this:

2010-07-26

MM should be upper case !
see
http://www.kbasic.com/doku.php?id=lrbuiltins#format

Try this:
msgbox format(now(),"yyyy-MM-dd hh:mm:ss")

[cpcarranza]

Hello !

I'm having similar problem...., and the above don't fix it

1.- I have a text file with this data:
1977-09-05

2.- I read it with this:
dim ini as string
Input #1, ini 'also if I use InputBox, have the next same results... or if I use Dim ini as string = "1977-09-05"

3.- Then I try to use
format (ini,"dddd dd-MM-yyyy")
...and get:
dddd dd-MM-yyyy1977-09-05

also I tried:
dim date1 as date = ini
format (date1,"dddd dd-MM-yyyy")
...and get blanks !

and....
dim date1 as date
date1 = DateValue(ini)
format (date1,"dddd dd-MM-yyyy")
...and get blanks !

The input will always be a String ! so I cannot use #1977-09-05#

so all in all...

How do I convert a string to a date value ? and give it format so it looks like: lunes 05-Sep-1977

Thanks !

[pappawinni]

Hi cp

tested and tested and

Code: Select all

Dim strDt as String
Dim dtDt As datetime


strDt = "1002-02-02"
dtDt = CDate(strDt)
?dtDt,strDt

? "1: " & format(CDate(strDt),"yyyy-MM-dd")
? "2: " & format(datevalue(strDt),"yyyy-MM-dd")
? "3: " & format(dtDt,"yyyy-MM-dd")
? "4: " & format(dateserial(year(dtDt), month(dtDt), day(dtDT) ),"yyyy-MM-dd") 
? "5: " & format(now(),"yyyy-MM-dd")
? "6: " & format(#10-01-22#,"yyyy-MMM-dd")


only results for 5: and 6:
seems to be a bug.

BERND kann das vielleicht irgendwie damit zu tun haben, dass es einerseits eine Funktion DATE() und anderereits den Datentyp Date gibt ??

[Slowdown]

@cparranza
hope you can use this, found it between my old VB codes from long ago and ported it to KBasic.
You have to polish the code, i didn't.

Code: Select all

Private Sub Form_OnOpen()
  log ReturnDate ("dd", "mmm", "yyyy", "01-09-2010")
End Sub


Private Function ReturnDate (DTOne as String, DTTwo as String, DTTree as String, MyDate as String) as String
  ' MyDate MUST be dd-mm-yyyy
  Dim MyMonth as String
  Dim MyDay as String
  Dim MyYear as String
  Dim ReturnValue as String
 
  ReturnValue = ""
  MyDay = left(MyDate,2)
  MyMonth = mid(myDate,4, 2)
  MyYear = right(MyDate,4)

  Select case ucase(DTOne)
    Case "YY"
      ReturnValue = right(MyYear,2)
    Case "YYYY"
      ReturnValue = MyYear
    Case "MM"
      ReturnValue = MyMonth
    Case "MMM"
      ReturnValue = NameOfMonth(val(Mymonth)) & " "
    Case "DD"
      ReturnValue = MyDay
    Case "DDD"
      ReturnValue = DayOffWeek(val(MyYear), val(MyMonth), val(MyDay)) & " " & MyDay
    End Select
  Select case ucase(DTTwo)
    Case "YY"
      ReturnValue = ReturnValue & "." & right(MyYear,2)
    Case "YYYY"
      ReturnValue = ReturnValue & "." & MyYear
    Case "MM"
      ReturnValue = ReturnValue & "." & MyMonth
    Case "MMM"
      ReturnValue = ReturnValue & " " & NameOfMonth(val(Mymonth)) & " "
    Case "DD"
      ReturnValue = ReturnValue & "." & MyDay
    Case "DDD"
      ReturnValue = ReturnValue & "." & DayOffWeek(val(MyYear), val(MyMonth), val(MyDay)) & " " & MyDay     
    End Select
  Select case ucase(DTTree)
    Case "YY"
      ReturnValue = ReturnValue & "." & right(MyYear,2)
    Case "YYYY"
      ReturnValue = ReturnValue & "." & MyYear
    Case "MM"
      ReturnValue = ReturnValue & "." & MyMonth
    Case "MMM"
      ReturnValue = ReturnValue & " " & NameOfMonth(val(Mymonth)) & " "
    Case "DD"
      ReturnValue = ReturnValue & "." & MyDay
    Case "DDD"
      ReturnValue = ReturnValue & "." & DayOffWeek(val(MyYear), val(MyMonth), val(MyDay)) & " " & MyDay
    End Select
  Return ReturnValue       
End Function 

Private Function NameOfMonth (MonthInt as Integer) as String
   Dim MyMonth[12] as Variant
   MyMonth = Array ["Jan", "Feb", "Mar", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
   Return MyMonth(MonthInt -1)
End Function 

Private Function DayOffWeek(dYear As Integer, dMonth As Integer, dDay As Integer) As String
   Dim Century            As Integer
   Dim tYear              As Integer
   Dim ResultDay          As Integer
   Dim MyDayOfWeek[6] As Variant
   MyDayOfWeek = Array ["Sun", "Mon", "Thu", "Wed", "Thur", "Fri", "Sat"]
   
   dMonth = dMonth - 2

   If dMonth < 1 Or dMonth > 10 Then
      dMonth = dMonth + 12
      dYear = dYear - 1
   End If

   Century = dYear \ 100                        ' get the century of the year.
   tYear = dYear Mod 100                        ' get the year (not the century)

   ResultDay = (Fix(2.6 * dMonth - 0.1) + dDay + tYear + (tYear \ 4) + (Century \ 4) - Century - Century) Mod 7

   If ResultDay <= 0 Then
      ResultDay = ResultDay + 7
   End If
   Return MyDayOfWeek(ResultDay)
End Function


[cpcarranza]

Great code Slowdown !

I made one like this:

Code: Select all

Private function FechaDMA2 (fechai as String) as String
  ' fechai must be: dd-mm-yyyy or dd/mm/yyyy
  Dim Inic as String, day1 as String, month1 as String
  Inic=Mid(fechai,7,4) + "-" + Mid(fechai,4,2) + "-" + Mid(fechai,1,2)
  select case weekday(Inic)
        case 2
          day1="Lunes"
        case 3
          day1="Martes"
        case 4
          day1="Miercoles"
        case 5
          day1="Jueves"
        case 6
          day1="Viernes"
        case 7
          day1="Sabado"
        case 1
          day1="Domingo"
        case else
          day1="..."
        end select
       select case month(Inic)
        case 1
          month1="Enero"         
        case 2
          month1="Febrero"
        case 3
          month1="Marzo"
        case 4
          month1="Abril"
        case 5
          month1="Mayo"
        case 6
          month1="Junio"
        case 7
          month1="Julio"
        case 8
          month1="Agosto"
        case 9
          month1="Septiembre"
        case 10
          month1="Octubre"
        case 11
          month1="Noviembre"
        case 12
          month1="Diciembre"                                       
        case else
          month1="..."
        end select
       return day1 & " "& day(inic) & " / " & month1 & " / " & YEAR(inic)
       'The output looks like:
       'Lunes 16 / Agosto / 2010
end function 

...but I think I will try to implement your parameters in my function, hope this help somebudy

P.S. I have a similar problem with FORMAT using " , " for numbers.... I want "1,000" but always get "1000"

[berndnoetscher]

Don't have time to have a look at it now and tomorrow. Please be patient until Friday...