Jump to content

Date in a query is wrong format

motormad
 Share

Recommended Posts

motormad Wayfarer

motormad

Posted
Posted

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

Link to comment
Share on other sites
  • Moderators
Melba23 Universalist

Melba23

Posted
  • Moderators
Posted

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites
motormad Wayfarer

motormad

Posted
  • Author
Posted

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.

;)

#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
Link to comment
Share on other sites
elektron Seeker

elektron

Posted
Posted

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.

;)

#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.

Link to comment
Share on other sites
Spiff59 Universalist

Spiff59

Posted
Posted

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)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...