I have this database where I store all my data in SQL dates and everything works perfectly fine… until i move that program to a computer that has date format different than the one SQL uses.
Obviously, I am aware ParseDate works with regional and bla bla settings. Therefore, I am looking for an easy to use method to make sure the date entered is a valid one (it exists…). I use a mask on my EditField which is “####-##-##” as per SQL date format of YYYY-MM-DD.
I have read quite a few threads about this… didn’t find much or they were all just too complicated. Not sure if I’ll get to find something easy for this though.
http://documentation.xojo.com/index.php/ParseDate
Anyone got something please?
You can use code like this
dim d as new date
d.SQLDateTime = text
to parse a SQL date time value.
Thx Christian!
This works fine to turn a string to a date format… say I enter 2013-07-34 it will return 2013-08-03. And that’s not quite what I was looking for though it could be handy…
I was more looking for a way to return a boolean value as to see if the date is a february 30 then it would return false.
I will use your method for now as I really need it now, but will keep looking for more.
I thought back at it and tried this:
dim d as new date
d.SQLDateTime = text
If d.SQLDateTime = text then
return false //date entered does not match an SQLDateTime format
Else
return true //dae entered is the same as the one returned by the parsing therefore is legit
End if
Is this ok?
Not written by me, but see if this does what you need:
Protected Function ValidDate(text as String, ByRef value As Date, assumePastFuture as integer= 0) As Boolean
// Written by Steve Garman
// http://rb.sgarman.net/validDate.php
// If the year provided has only one or 2 digits, check assumePastFuture
// negative value means the past, positive means future, 0 means current century
// If no year is supplied, assumePastFuture has a granularity of 1 year
Static yearPos as Integer = -9
Static monthPos as Integer = -9
Static dayPos as Integer = -9
if yearPos = -9 then // first time through
yearPos = -1 // only try this once
// try to work out local date format
// assume Gregorian calendar
// assume shortDate contains all 3 numbers
// don't use NthField or Split in case it contains other characters
dim d as new date
// clear any time numbers, in case of unusual shortDate format
d.TotalSeconds = 0
// set unique values for year, month & date
d.SQLDate = "2005-12-31"
dim s as String = d.ShortDate
dim pos() As integer
dim thisPos as Integer
thisPos = InStr( 0, s, "05" )
if thisPos > 0 then pos.Append thisPos
thisPos = InStr( 0, s, "12" )
if thisPos > 0 then pos.Append thisPos
thisPos = InStr( 0, s, "31" )
if thisPos > 0 then pos.Append thisPos
if UBound( pos ) = 2 then
// we've found all three elements
// sort them by position in shortDate
dim typ() As string = Array( "y", "m", "d" )
pos.SortWith typ
yearPos = typ.IndexOf( "y" )
monthPos = typ.IndexOf( "m" )
dayPos = typ.IndexOf( "d" )
end if
end if
if yearPos < 0 or monthPos < 0 or dayPos < 0 then
// we don't know how to parse the date
// some might want to set defaults instead of returning false
Return false
end if
// now check the date has just numbers and two delimiters
Dim sep as String
Dim tmp as String
Dim i as Integer
Dim noYearSupplied as Boolean
tmp = text
// first figure out what separator they gave us .. have to both be the same one
for i = 0 to 9
tmp = replaceAll(tmp,format(i,"0"),"")
next
select case len(tmp)
case 0
// unable to understand the format entered
return false
case 1
sep = tmp
case 2
sep = mid(tmp,1,1)
if sep <> mid(tmp,2,1) then
// invalid - two different separators
return false
end if
else
return false
end select
//make array of elements
Dim dats() as String = Split( text, sep )
if UBound( dats ) <> 2 then
// add in the missing year ?
dim tmpDate as new date
dats.Insert yearPos, format(tmpDate.year,"0000")
noYearSupplied = True
end if
if UBound( dats ) <> 2 then
//invalid date - should never get here.
Return false
end if
dim yr As integer = CDbl( dats( yearPos ) )
if yr < 100 then
// fix short year by assuming current century
// proving that we learned nothing from y2k
dim today as new Date
dim century as integer
century = today.year \\ 100
century = century * 100
yr = yr + century
// use any assumptions about whether the date is past or future to set century
if assumePastFuture < 0 then
if yr > today.Year then
yr = yr - 100
end if
elseif assumePastFuture > 0 and yr < today.Year then
yr = yr + 100
end if
dats( yearPos ) = CStr( yr )
elseif noYearSupplied then
// use any assumptions about whether the date is past or future to set year
dim mth as integer = CDbl( dats( monthPos ) )
dim dy as integer = CDbl( dats( dayPos ) )
dim today as new Date
if assumePastFuture < 0 then
if mth > today.Month or ( mth = today.Month and dy > today.Day ) then
yr = yr - 1
end if
elseif assumePastFuture > 0 and ( mth < today.Month or ( mth = today.Month and dy < today.Day ) ) then
yr = yr + 1
end if
dats( yearPos ) = CStr( yr )
end if
// put detail into a date object
dim retVal as new date
dim yy,mm,dd as Integer
yy = val( dats( yearPos ) )
mm = val( dats( monthPos ) )
dd = val( dats( dayPos ) )
retVal.TotalSeconds = 0
retVal.Year = yy
retVal.Month = mm
retVal.Day = dd
// check the date object is not making corrections
if retVal.Year <> yy or retVal.Month <> mm or retVal.Day <> dd then
//probably an invalid day of the month
Return false
end if
//populate value ( ByRef side-effect )
if value = nil Then
value = new Date
end if
value.totalseconds = retVal.TotalSeconds
return true
End Function
Thanks Tim and Kem.
That function looks pretty complete, but most of the testing it does, is automatically processed through a mask “####-##-##”, since I always use SQL model. Bcuz it looks like that function tries to find the local date format.
Overall, I thank you all for your help!! Much appreciate it