Date in a query is wrong format

[motormad]

How do i make sure that my date in a query is amways the correct format?

The US and UK is making it verry dificult for me.

$STA_D = GUICtrlCreateDate("2012/05/01 04:34:26", 183, 125, 86, 21,0)
$STO_D = GUICtrlCreateDate("2012/06/08 04:34:26", 278, 125, 86, 21,0)
$Start_D= GUICtrlRead($STA_D )
$Stop__D= GUICtrlRead($STO_D )
ConsoleWrite(@CRLF)
ConsoleWrite ("GUICtrlRead($STA_D ) = " &  GUICtrlRead($STA_D ) & @CRLF)
ConsoleWrite ("GUICtrlRead($STO_D ) = " &  GUICtrlRead($STO_D ) & @CRLF)
ConsoleWrite ("$Start_D = " &  $Start_D & @CRLF)
ConsoleWrite ("$Stop__D " &  $Stop__D & @CRLF)
$query1 = "SELECT * FROM Tbl_Bonnen WHERE (((Tbl_Bonnen.[Datum afwerking]) Between #" & $Start_D & "# And #" & $Stop__D & "#));"

$query1 = "SELECT Tbl_Bonnen.*, Tbl_Bonnen.[Datum afwerking] FROM Tbl_Bonnen WHERE (((Tbl_Bonnen.[Datum afwerking]) Between #"&GUICtrlRead($STA_D) &"# And #"&GUICtrlRead($STO_D) &"#));"

i posted some code.

The query is used with an acces database.

but the month and days are switching places.

thanxs in advance

[Melba23]

motormad,

The UK uses DD/MM/YYYY - only the US uses that ludicrous MM/DD/YYYY system.

But this _Date_Time_Convert UDF from Malkey should help you get everything in whichever format you need.

M23

[elektron]

In my personal experience dealing with databases, I've always tried to keep the timestamps stored in unix epoch time (if possible). It just makes conversions back and forth so much easier.

[abberration]

I always thought the best way to do dates should be YYYY/MM/DD. That way, a sorted list would always be in correct chronological order.

[motormad]

Thanx all,

Melba23, Im gonna try this _Date_Time_Convert UDF

Elektron, can you explain a little more?

abberration, in this case it wont help me out.

greetz

[motormad]

Stil got 1 problem.

And i did find why it wont work.

But dont have a solution.

Sometimes de day is 1 digit.

If that happens.

It wil convert in the wrong date.

expandcollapse popup

#include <Date.au3>
#include <Date_Time_Convert.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <DateTimeConstants.au3>
Local $st
Local $Start_D

#Region ### START Koda GUI section ### Form=
Local $Form1_1 = GUICreate("Form1", 499, 284, 192, 124)
Local $STA_D = GUICtrlCreateDate("2012/01/13 12:30:16", 183, 125, 100, 21, $DTS_SHORTDATEFORMAT)
Local $Button1 = GUICtrlCreateButton("Button1", 215, 170, 51, 31)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
;;================================================================================
;;AUTOIT SWITCH
;;================================================================================
local $Msg , $Exit
While 1
$msg = GUIGetMsg()
    Select
        Case 0
            ContinueLoop
        Case $msg = $GUI_EVENT_CLOSE
   Exit
  Case $msg = $Button1
   schijf()
Endselect
WEnd
;;================================================================================
;;AUTOIT SWITCH
;;================================================================================

func schijf()
Local $sFormatIn = "dd/MM/yyyy"
Local $sFormatOut = "MM/dd/yyyy"
Local $Start_D
$Start_D = _Date_Time_Convert(GUICtrlRead($STA_D),$sFormatIn, $sFormatOut)
ConsoleWrite ("GUICtrlRead($STA_D) " &  GUICtrlRead($STA_D)   &  " $Start_D : " & $Start_D & @CRLF)
EndFunc

[elektron]

Stil got 1 problem.

And i did find why it wont work.

But dont have a solution.

Sometimes de day is 1 digit.

If that happens.

It wil convert in the wrong date.

expandcollapse popup

#include <Date.au3>
#include <Date_Time_Convert.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <DateTimeConstants.au3>
Local $st
Local $Start_D

#Region ### START Koda GUI section ### Form=
Local $Form1_1 = GUICreate("Form1", 499, 284, 192, 124)
Local $STA_D = GUICtrlCreateDate("2012/01/13 12:30:16", 183, 125, 100, 21, $DTS_SHORTDATEFORMAT)
Local $Button1 = GUICtrlCreateButton("Button1", 215, 170, 51, 31)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
;;================================================================================
;;AUTOIT SWITCH
;;================================================================================
local $Msg , $Exit
While 1
$msg = GUIGetMsg()
    Select
        Case 0
            ContinueLoop
        Case $msg = $GUI_EVENT_CLOSE
   Exit
  Case $msg = $Button1
   schijf()
Endselect
WEnd
;;================================================================================
;;AUTOIT SWITCH
;;================================================================================

func schijf()
Local $sFormatIn = "dd/MM/yyyy"
Local $sFormatOut = "MM/dd/yyyy"
Local $Start_D
$Start_D = _Date_Time_Convert(GUICtrlRead($STA_D),$sFormatIn, $sFormatOut)
ConsoleWrite ("GUICtrlRead($STA_D) " &  GUICtrlRead($STA_D)   &  " $Start_D : " & $Start_D & @CRLF)
EndFunc

When you store things in unix epoch time, you're storing a date and time as the number of seconds since Jan 1, 1970 00:00:00 UTC.

See: http://en.wikipedia.org/wiki/Unix_time

The conversions are much easier. If you're storing someone's birthday or getting a date input from the user, it is best to use a list selection or combo selection to retrieve that input... so it's entered the same exact way, every single time.

Now, given your current situation, I guess it would be a big hassle to think about time conversions back and forth. You've already identified that you have two errors (that you know of) in your program adversely affecting the program's output, and you could work with those a different way...

Think about this:

1) You could build a relationship between the country of the input's origin, and the time format. (Use an inner join on your database) Use that data to determine how the time is going to be entered in your existing format.

2) Another thing that you could do, when you go to parse the dates into variables, is to write a function "returnCorrectDate($szDate)" that checks what format the date is in using some regex or etc, and return a corrected version

of the date that your program will use for the rest of its input.

Just some food for thought.

[Spiff59]

Aren't a couple lines sufficient?

$Start_In = "1/13/2012" ; GUICtrlRead($STA_D)
$Start_Out = StringSplit($Start_In, "/")
$Start_Out = $Start_Out[2] & "/" & $Start_Out[1] & "/" & $Start_Out[3]
ConsoleWrite ($Start_In   &  $Start_Out & @CRLF)