 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / FoxPro / General FoxPro Topics / July 2008Date to Integer Q.
Is it possible to turn a date to an integer? jm* Sure! Subtract it from (or add it to) any number. You're asking a lot of questions around this that lead me to suspect you're grasping at straws. Rather than asking about a specific technique, why don't you describe what you're trying to *do*. I'm willing to bet there are ways that don't involve monkeying with turning dates into numbers. :-) Dan > Is it possible to turn a date to an integer? > > jm* Dan, As always, I appreciate your help. I am just trying to make sure that when I takeVFP data to Excel that the date fields are sortable. When I have a date field that copies to excel it becomes sortable only by the month. I am using the DTOS() to kick the date for sortable purposes but then it looks strange to the user (ie. 04/01/08 becomes 20080401) but it beats the unsortable. Any knowledge that you can part with is very helpful. Jonathan Morningstar > Sure! Subtract it from (or add it to) any number. > [quoted text clipped - 9 lines] >> >> jm* So how do you export dates to Excel that it does not work? Bye, Olaf. Hi Jonathan, maybe you get it, if I post it the third time? create cursor curDates (ddate D Default Date()) append blank append blank append blank append blank update curDates set dDate = Date()+Rand(0)*20 export to some.xls type xl5 Dates are sortable now. Bye, Olaf. > update curDates set dDate = Date()+Rand(0)*20 unfortunately doesn't create random dates. Well whatever, export to xl5 works for me, xlS not. Bye, Olaf. I've seen others ask you how you're doing this export. Olaf asks now. Will you answer? :-) Excel is pretty smart at handling dates, but it is at time TOO smart for its own good unless you format the column properly. Dan > Dan, > As always, I appreciate your help. I am just trying to make sure [quoted text clipped - 21 lines] >>> >>> jm* Hi Jonathan, > As always, I appreciate your help. I am just trying to make sure that > when I takeVFP data to Excel that the date fields are sortable. When I > have a date field that copies to excel it becomes sortable only by the > month. I am using the DTOS() to kick the date for sortable purposes but > then it looks strange to the user (ie. 04/01/08 becomes 20080401) but it > beats the unsortable. For me several messages are missing. One solution to make dates sortable is to transform them into a sortable string format like DTOS does, the other solution is making excel handling the date values correctly. EXPORT TO XL5 does make a newer Excel format as EXPORT TO XLS, and this newer Excel format can cope with date values correctly, eg sort dates correctly But if you (still) do DTOS, it remains strings and the changed excel format does not change these strings back to dates. Simply leave it dates in the original dbf and use EXPORT TO XL5. Bye, Olaf. > Hi Jonathan, > [quoted text clipped - 6 lines] > > For me several messages are missing. The filters have been misbehaving badly lately. Also, if you're using Windows Mail on Vista and have the communities feature turned on, TURN IT OFF! Dan > Also, if you're using Windows Mail on Vista and have the communities > feature turned on, TURN IT OFF! At home, true. But here in the office we still use Win XP pro. And I miss messages at both computers. Bye, Olaf. TRANSFORM(DTOS(DATE()),"@R XXXX/XX/XX") That will look better with the slashes. > Dan, > As always, I appreciate your help. I am just trying to make sure that when [quoted text clipped - 21 lines] > >> > >> jm* You cannot subtract a date from a number, the subtraction operator will not overload in this manner. The addition operation will over load in both directions, interesting enough. I'm guessing it does this because addition is commutative and subtraction is not. When you add a date and a number n, it will increment the date by n, regardless of the order in which you express it. When VFP exports to type XLS, it converts dates to char, specfically "dd-mm-yy". When you export to type XL5, it exports dates as integer with the cell formatted as a date type. That is why you have to export to xl5 if you want to sort by date. > Sure! Subtract it from (or add it to) any number. > [quoted text clipped - 9 lines] >> >> jm* > Is it possible to turn a date to an integer? > > jm* Excel stores dates in the form of Serial Date Number, which are the number of days since 1/1/1900. Thus: 1/1/1900 = 1 1/31/1900 = 31 2/29/1900 = 29 7/25/2008 = 39654 etc. The more astute will protest that 2/29/1900 is an invalid date, and so it is - but spreadsheets have been based on that error since (at least) the days of Lotus. To convert a date to a serial date value, just subtract the start date and add 2: {^2008-7-25} - {^1900-1-1} + 2 = 39654 For dates prior to March 1, 1900, add 1 instead of 2. More on Excel date/times, as well as info on the 1900 "leap year" can be found here: http://www.ozgrid.com/Excel/ExcelDateandTimes.htm - Rush |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.