Hello,
Brian's back! I have this field containing a date, looking like this: 20140711
I need to add one year on (ie, 365 days) and write the new date into another field. Anybody got a routine
or have an idea how to do this?
BTW, it's not an SQL database, just a csv text file
Thanks a lot,
Brian
Are you wanting to add a year
1. ignoring leap years or
2. account for leap years or
3. strictly add 365 days?
STRING OldDate = "20140711"
STRING NewDate
NewDate = STR$(VAL(LEFT$(OldDate, 4)) + 1) + MID$(OldDate, 5, LEN(OldDate))
Hi, Larry,
Well, it would have to include leap years for it to be correct. There is a sample posted here somewhere,
but I tried it last night, and the returned day was out by one day, and eventually it crashes
Sam: Good try, but you have to turn your source date into a Julian number, add the days required,
and turn that number back into a readable date. I wish it was that easy!
Brian
These routines were written fo CBasic but are easy enough to adapt.
sub getJulian(useYear:int,useMonth:int,useDay:int)
' Returns the Julian Date from year,month,day input
ly = isLeapYear(useYear)
'not counting current year
YearsPassed = useYear - 1901
DaysInFullYears = YearsPassed * 365
' The function is only valid from 1/1/1901 to 31/12/2099 so shortcut leap year calc is OK
LeapYearsPassed = int(YearsPassed/4)
julian = DaysInFullYears + LeapYearsPassed - 1
yearToDate=0
select useMonth
case 1
yearToDate = 0
case 2
yearToDate = 31
case 3
yearToDate = 59 + ly
case 4
yearToDate = 90 + ly
case 5
yearToDate = 120 + ly
case 6
yearToDate = 151 + ly
case 7
yearToDate = 181 + ly
case 8
yearToDate = 212 + ly
case 9
yearToDate = 243 + ly
case 10
yearToDate = 273 + ly
case 11
yearToDate = 304 + ly
case 12
yearToDate = 334 + ly
endselect
retval = julian + yearToDate + useDay
return retval
sub getDate$(jDate;int, localsetting:int),string
' Return the date from jDate
' localsetting 1 is mm/dd/yyyy format
' local setting 2 is dd/mm/yyyy
TheYear = 0:TheMonth = 0:TheDay = 0
aYears = int(jDate/365.25)+1
year = 1901:jDate = jDate + 1:ly = 0
FOR i = 1 to aYears
IF jDate <= (365 + ly)
TheYear=year
goto endfor
ENDIF
jDate =jDate - (365+ly)
year = year + 1
ly = isLeapYear(year)
LABEL endfor
NEXT i
IF jDate <= 0
' "Sorry but that date is out of my range"
retval$ = "bad date"
goto endjDate
ENDIF
'select jDate
IF jDate <= 31
'january
TheMonth = 1
TheDay = jDate
ENDIF
IF jDate > 31 & jDate <= 59 + ly
'february
TheMonth = 2
TheDay = jDate - 31 + ly
ENDIF
IF jDate > 59 + ly & jDate <= 90 + ly
'march
TheMonth = 3
TheDay = jDate - 59 + ly
ENDIF
IF jDate > 90 + ly & jDate <= 120 + ly
'april
TheMonth = 4
TheDay = jDate - 90 + ly
ENDIF
IF jDate > (120 + ly) & jDate <= (151 + ly)
' may
TheMonth = 5
TheDay = jDate - 120 + ly
ENDIF
IF jDate > 151 + ly & jDate <= 181 + ly
' june
TheMonth = 6
TheDay = jDate - 151 + ly
ENDIF
IF jDate > 181 + ly & jDate <= 212 + ly
' july
TheMonth = 7
TheDay = jDate - 181 + ly
ENDIF
IF jDate > 212 + ly & jDate <= 243 + ly
' august
TheMonth = 8
TheDay = jDate - 212 + ly
ENDIF
IF jDate > 243 + ly & jDate <= 273 + ly
' september
TheMonth = 9
TheDay = jDate - 243 + ly
ENDIF
IF jDate > 273 + ly & jDate <= 304 + ly
' october
TheMonth = 10
TheDay = jDate - 273+ly
ENDIF
IF jDate > 304 + ly & jDate <= 334 + ly
' november
TheMonth = 11
TheDay = jDate - 304 + ly
ENDIF
IF jDate > 334 + ly & jDate <= 365 + ly
' december
TheMonth = 12
TheDay = jDate - 334 + ly
ENDIF
theDay$ = ltrim$(str$(theDay))
theMonth$ = ltrim$(str$(theMonth))
theYear$ = ltrim$(str$(theYear))
' output can be varied here to suit whatever format you want
IF len(theDay$) = 1 THEN theDay$ = "0"+theDay$
IF len(theMonth$) = 1 THEN theMonth$ = "0"+theMonth$
IF localsetting = 2
temp$ = Append$(theDay$,"/",TheMonth$,"/",theYear$)
retval$ = temp$
ENDIF
IF localsetting = 1
temp$ = Append$(theMonth$,"/",TheDay$,"/",theYear$)
retval$ = temp$
ENDIF
LABEL endjDate
RETURN retval$
sub isLeapYear(yyyy:int),int
retval = yyyy%4 = 0 & yyyy%100 <> 0 | yyyy%400 = 0
return retval
Quote from: Brian Pugh on July 16, 2014, 05:27:39 AM
Sam: Good try, but you have to turn your source date into a Julian number, add the days required,
and turn that number back into a readable date. I wish it was that easy!
My bad. In that case you can use the Windows System and File Time API functions. Here's a compilable :
$INCLUDE "windowssdk.inc"
TYPE DateType
UINT LowDW
UINT HighDW
END TYPE
UNION uDT
UINT64 qVar
DateType dtVar
END UNION
$MAIN
SYSTEMTIME st
FILETIME ft
uDT u
INT rtn
UINT64 qDAY = 24 * 60 * 60 * 10000000 ' 1 day in nano seconds
' First get the system time so that all the other structure members are filled.
GetSystemTime(st)
'Setup the SYSTEMTIME structure with the original date like so:
st.wMonth = 7: st.wDay = 11: st.wYear = 2014
PRINT USING("Original date = ##/##/####", st.wMonth, st.wDay, st.wYear)
' Convert the system time to file time.
rtn = SystemTimeToFileTime(st, ft)
' Move the FILETIME info into a UINT64 variable
' and add days using the Quad variable. (This is how M$ recommends doing it)
u.dtVar.LowDW = ft.dwLowDateTime
u.dtVar.HighDW = ft.dwHighDateTime
'Add days using the Quad variable.
u.qVar += (qDay * 2) '<---- Add 2 days
ft.dwLowDateTime = u.dtVar.LowDW
ft.dwHighDateTime = u.dtVar.HighDW
' Convert the file time to system time.
rtn = FileTimeToSystemTime(ft, st)
' Now your new date will be in the SYSTEMTIME structure members.
PRINT USING("##/##/####", st.wMonth, st.wDay, st.wYear)
END
Update:
I've discovered a problem: Don't use the variable qDATE. Instead, use the numbers it was assigned: 24*60*60*10000000. That will work.
Maybe a bug with UINT64 data type. I don't know.
Added: The problem with the quad integer results from the unconventional assignment. If I just assign the number like this qDATE = 8.64E11, it works fine.
I noticed another compiler didn't like that assignment either. That compiler warned that there would be an integer overflow. I expect that's what happened.
I only wrote it that way to illustrate how a single day is constructed with nanoseconds.
So faith restored in UINT64. :)
Sam
Thanks, Sam,
That looks rather nifty! I'll give it a whirl tonight . . .
Brian