[cfaussie] Re: Date format
Tom,
If you use the following, which is an extract from a component I wrote
to convert Active Directory account expiry filetime to utc time, you
get quite different results.
AD stores dates as nanoseconds since 1/1/1601, but I'd say that these
dates are probably seconds since 1/1/1970 or seconds from 30/12/1899,
so I've cut out the part that deals with nanoseconds! .
cfscript code snippet:
interimDate1 = DateAdd(s, 716640, 30/12/1899 00:00:00);
interimDate2 = DateAdd(s, 712672, 30/12/1899 00:00:00);
interimDate3 = DateAdd(s, 716640, 01/01/1970 00:00:00);
interimDate4 = DateAdd(s, 712672, 01/01/1970 00:00:00);
// Convert UTC to local.
convertedDate1 = createodbcdatetime(DateConvert(utc2local,
interimDate1));
convertedDate2 = createodbcdatetime(DateConvert(utc2local,
interimDate2));
convertedDate3 = createodbcdatetime(DateConvert(utc2local,
interimDate3));
convertedDate4 = createodbcdatetime(DateConvert(utc2local,
interimDate4));
If you output these dates you get you get the same actual days, but
different times.
{ts '1900-01-07 17:04:00'}
{ts '1900-01-07 15:57:52'}
{ts '1970-01-09 17:04:00'}
{ts '1970-01-09 15:57:52'}
Which date range do you think tour dobs are from? You should be able
to work out which one of these to use.
Doesn't quite make sense to me that both Excel and CF are showing the
date years 3862 and 3851. They have to be working in seconds maybe,
not days since a certain date.
Hope this helps.
Pauline
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~--~~~~--~~--~--~---
[cfaussie] Re: Date format
For anyone still interested in this thread, I thought I might show my final
conclusions...
The database is called Bluechip (a product of HCN, the folks who brought you
Medical Director) and anyone working in the health industry may come across
it.
To get a date from a Bluechip date integer subtract the constant 693594 from
the integer and hand over to CF.
e.g. #dateformat(DOB-693594,d/m/)#
Strangely, if DOB is null, the result is 2/1/0001
Thanks to everyone for their help and suggestions.
Tom
On 4/5/07, paulineb [EMAIL PROTECTED] wrote:
Tom,
If you use the following, which is an extract from a component I wrote
to convert Active Directory account expiry filetime to utc time, you
get quite different results.
AD stores dates as nanoseconds since 1/1/1601, but I'd say that these
dates are probably seconds since 1/1/1970 or seconds from 30/12/1899,
so I've cut out the part that deals with nanoseconds! .
cfscript code snippet:
interimDate1 = DateAdd(s, 716640, 30/12/1899 00:00:00);
interimDate2 = DateAdd(s, 712672, 30/12/1899 00:00:00);
interimDate3 = DateAdd(s, 716640, 01/01/1970 00:00:00);
interimDate4 = DateAdd(s, 712672, 01/01/1970 00:00:00);
// Convert UTC to local.
convertedDate1 = createodbcdatetime(DateConvert(utc2local,
interimDate1));
convertedDate2 = createodbcdatetime(DateConvert(utc2local,
interimDate2));
convertedDate3 = createodbcdatetime(DateConvert(utc2local,
interimDate3));
convertedDate4 = createodbcdatetime(DateConvert(utc2local,
interimDate4));
If you output these dates you get you get the same actual days, but
different times.
{ts '1900-01-07 17:04:00'}
{ts '1900-01-07 15:57:52'}
{ts '1970-01-09 17:04:00'}
{ts '1970-01-09 15:57:52'}
Which date range do you think tour dobs are from? You should be able
to work out which one of these to use.
Doesn't quite make sense to me that both Excel and CF are showing the
date years 3862 and 3851. They have to be working in seconds maybe,
not days since a certain date.
Hope this helps.
Pauline
--
IMPORTANT: This email is intended for the use of the individual addressee(s)
named above and may contain information that is confidential privileged or
unsuitable for overly sensitive persons with low self-esteem, no sense of
humor or irrational religious beliefs. If you are not the intended
recipient, any dissemination, distribution or copying of this email is not
authorized (either explicitly or implicitly) and constitutes an irritating
social fauxpas. No animals were harmed in the transmission of this email,
although the mutt next door is living on borrowed time, let me tell you.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~--~~~~--~~--~--~---
[cfaussie] Re: Date format
Ok, It will be a julian date of some format, which is normally a number or seconds or minutes past a certain point in time, based on the small number, minutes. The standard Java Time is seconds or milliseconds past 1 Jan 1970. I tried this with yours but it didn't work, you need to know the base, so play with this. cfset me = createDate(1970, 1, 1) / cfset me = dateAdd(s, 71664, me) / cfoutput#me#/cfoutput And you can probably work out the base, assuming you are confident Excel converted it correctly. If you still have access to the old system try adding a record and see what the number comes out as, then try to adjust such that the number gets closer to 0, which will be the base. Regards Dale Fraser http://dale.fraser.id.au/blog _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom MacKean Sent: Tuesday, 3 April 2007 5:18 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Date format Hi all, I am using ColdFusion to drag some data kicking and screaming out of a very strange MS-based database. The date of birth field has values such as 716640 and 712672. After a bit of mucking about I discovered that I can place this value in an excel cell and change the format to date to give a value that seems to makes sense. (The two values above yield 02.02.62 and 24.3.51 respectively). Does anyone know what is going on here - and more importantly what can I do in CF to 716640 to make it a date that I can use as a normal date. Cheers, Tom -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
Trying to create a date of 0001/01/01 is alot of fun in CF - I cant seem to create one using createdate(). I imagine Tom you will have to use something like the code below to generate a reference date to compare the integers against. cfset mydate = createdate(1962,2,2) cfset refdate = dateadd(d, -716640, mydate) cfoutput #dateformat(refdate, '/mm/dd')# /cfoutput hth Rod --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
The funny thing is, Excel recognises these numbers as dates. If I paste one of the numbers into Excel, then do Format Cell Date and choose the last option on the list, Excel will happily turn it into a correct date. To my thinking, it must therefore be a fairly standard way of formatting a date and yet I can find nothing about it anywhere. Very weird. More Googling needed I think. Thanks for all your help. T On 4/3/07, rod higgins [EMAIL PROTECTED] wrote: Trying to create a date of 0001/01/01 is alot of fun in CF - I cant seem to create one using createdate(). I imagine Tom you will have to use something like the code below to generate a reference date to compare the integers against. cfset mydate = createdate(1962,2,2) cfset refdate = dateadd(d, -716640, mydate) cfoutput #dateformat(refdate, '/mm/dd')# /cfoutput hth Rod -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
OK. Feeling a bit silly now. #dateformat(718167,d.m.yy)# works a treat. It gives 9.4.66 which is the same result as Excel (and I assume is correct) My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The key is that the year is only two digits. Sorry for wasting everyone's time :( T On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: The funny thing is, Excel recognises these numbers as dates. If I paste one of the numbers into Excel, then do Format Cell Date and choose the last option on the list, Excel will happily turn it into a correct date. To my thinking, it must therefore be a fairly standard way of formatting a date and yet I can find nothing about it anywhere. Very weird. More Googling needed I think. Thanks for all your help. T On 4/3/07, rod higgins [EMAIL PROTECTED] wrote: Trying to create a date of 0001/01/01 is alot of fun in CF - I cant seem to create one using createdate(). I imagine Tom you will have to use something like the code below to generate a reference date to compare the integers against. cfset mydate = createdate(1962,2,2) cfset refdate = dateadd(d, -716640, mydate) cfoutput #dateformat(refdate, '/mm/dd')# /cfoutput hth Rod -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
Barry I think we all do. I did the same thing a few years ago to, and even posted a solution to this from a mssql database. Tom: Yeah it is a Julian format, and is commonly used in storing dates in mySql servers. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Wednesday, 4 April 2007 10:42 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Date format no waste at all, IMHO. I stumbled across this with dateformat() by pure accident three years ago and then completely forgot. had you not reminded me with your problem I probably would have forgotten this for good. thanx for the mental jog (there's always a silver lining to look for) b On 4/4/07, Tom MacKean [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: OK. Feeling a bit silly now. #dateformat(718167,d.m.yy)# works a treat. It gives 9.4.66 which is the same result as Excel (and I assume is correct) My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The key is that the year is only two digits. Sorry for wasting everyone's time :( T On 4/4/07, Tom MacKean [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: The funny thing is, Excel recognises these numbers as dates. If I paste one of the numbers into Excel, then do Format Cell Date and choose the last option on the list, Excel will happily turn it into a correct date. To my thinking, it must therefore be a fairly standard way of formatting a date and yet I can find nothing about it anywhere. Very weird. More Googling needed I think. Thanks for all your help. T On 4/3/07, rod higgins [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Trying to create a date of 0001/01/01 is alot of fun in CF - I cant seem to create one using createdate(). I imagine Tom you will have to use something like the code below to generate a reference date to compare the integers against. cfset mydate = createdate(1962,2,2) cfset refdate = dateadd(d, -716640, mydate) cfoutput #dateformat(refdate, '/mm/dd')# /cfoutput hth Rod -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The Looks like a bug in the dateformat function. Perhaps you could raise an issue with Adobe about it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
Actually Excel returns the same result when you force it to use a four digit year. Tom On 4/4/07, Chris Velevitch [EMAIL PROTECTED] wrote: On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The Looks like a bug in the dateformat function. Perhaps you could raise an issue with Adobe about it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095 www.flashdev.org.au -- IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social fauxpas. No animals were harmed in the transmission of this email, although the mutt next door is living on borrowed time, let me tell you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
I can't see a bug at all. The dateformat function simply diplays the last two digits of the year of the date as it should. It the date is stored as 9.4.3866 you would expect a display of 9.4.66 wouldn't you? What I want to know is why #dateformat(1,d.m.)# is 31/12/1899 and not 1/1/900 ?? On Apr 4, 11:07 am, Chris Velevitch [EMAIL PROTECTED] wrote: On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The Looks like a bug in the dateformat function. Perhaps you could raise an issue with Adobe about it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
suggest you refer back to Karen's previous entry and then follow up the joelonsoftware link, about halfway donn the joelonsoftware post. it might help On 4/4/07, rod higgins [EMAIL PROTECTED] wrote: I can't see a bug at all. The dateformat function simply diplays the last two digits of the year of the date as it should. It the date is stored as 9.4.3866 you would expect a display of 9.4.66 wouldn't you? What I want to know is why #dateformat(1,d.m.)# is 31/12/1899 and not 1/1/900 ?? On Apr 4, 11:07 am, Chris Velevitch [EMAIL PROTECTED] wrote: On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The Looks like a bug in the dateformat function. Perhaps you could raise an issue with Adobe about it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
My point exactly excel parses the date from 1/1/1900, so should CF. The date stored in excel is the same as CF, for Tom's example it would be stored internally as 9/4/3866 regardless of how you display it. Half way down the article; In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today's date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1. So why does CF regard the integer 1 as 31/12/1899? Do others experience this or is it just a dodgy version of CF running on my localhost? -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Wednesday, 4 April 2007 12:17 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Date format suggest you refer back to Karen's previous entry and then follow up the joelonsoftware link, about halfway donn the joelonsoftware post. it might help On 4/4/07, rod higgins [EMAIL PROTECTED] wrote: I can't see a bug at all. The dateformat function simply diplays the last two digits of the year of the date as it should. It the date is stored as 9.4.3866 you would expect a display of 9.4.66 wouldn't you? What I want to know is why #dateformat(1,d.m.)# is 31/12/1899 and not 1/1/900 ?? On Apr 4, 11:07 am, Chris Velevitch [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote: My confusion initially was that #dateformat(718167,d.m.)# gives 9.4.3866 which didn't look right at all so I thought is wasn't working. The Looks like a bug in the dateformat function. Perhaps you could raise an issue with Adobe about it. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group m: 0415 469 095www.flashdev.org.au --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Date format
Tom,
The number is simple it is a date that is number of seconds from a given
date, if you type the number into excel it is going to do its best to
convert that number to a date.
The actual problem as I tried to find the code that I came up with fro
coldfusion, is simple to achieve.
The solution would be to do cfset date = DateAdd('s',value,01/01/1980) /
And that will give you the correct date from the time specified in Julian
dates converted to CF, excel doesn't do this for you it just tries to
convert number to a date object.
Andrew Scott
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of Tom MacKean
Sent: Wednesday, 4 April 2007 11:18 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Re: Date format
Actually Excel returns the same result when you force it to use a four digit
year.
Tom
On 4/4/07, Chris Velevitch [EMAIL PROTECTED] wrote:
On 4/4/07, Tom MacKean [EMAIL PROTECTED] wrote:
My confusion initially was that #dateformat(718167,d.m.)# gives
9.4.3866 which didn't look right at all so I thought is wasn't working.
The
Looks like a bug in the dateformat function. Perhaps you could raise
an issue with Adobe about it.
Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au
br
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~--~~~~--~~--~--~---
