Sign in to follow this  
Followers 0
motormad

Date in a query is wrong format

8 posts in this topic

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

Share this post


Link to post
Share on other sites



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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

1 person likes this

RAID Calculator | Software Installer

The truth has been suppressed since the dawn of time.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
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
Sign in to follow this  
Followers 0