Thankyou. I was rather thinking I might have to convert everything to
sec (I have no choice in this case but to use MS SQL) but as this is
going to be a bit of a job it's nice to know it is a sensible thing to
do.
Jochem van Dieten wrote:
>
> Richard Meredith-Hardy wrote:
> >
> > In converting over a timesheets application from Access to MSSQL I am
> > having difficulty with datetime fields: consider the following (in
> > CF5):
> >
> > <CFSET t1 = createtime("00","00","00")>
> > output: {ts '1899-12-30 00:00:00'}
> > <CFSET t2 = createtime("12","00","00")>
> > output: {ts '1899-12-30 12:00:00'}
> > <CFSET t3 = createtime("00","30","00")>
> > output: {ts '1899-12-30 00:30:00'}
> > <CFSET t4 = t1 + t2>
> > output: 0.5
> > <CFSET t4a = t2 + t3>
> > output: 0.520833333333
> > <CFSET t4b = createodbctime(t2 + t3)>
> > output: {t '12:30:00'}
> > <CFSET t4c = createodbcdatetime(t2 + t3)>
> > output: {ts '1899-12-30 12:30:00'}
> >
> > The values above are all saved to a MSSQL DB in datetime fields, this is
> > the output:
> >
> > t1: 1899-12-30 00:00:00
> > t2: 1899-12-30 12:00:00
> > t3: 1899-12-30 00:30:00
> > t4: 1900-01-01 12:00:00
> > t4a: 1900-01-01 12:29:59
> > t4b: 2004-09-19 12:30:00
> > t4c: 1899-12-30 12:30:00
> >
> > As you can see, there are various inconsistencies here
>
> I see only one inconsistency.
> Obviously t1, t2, t3 and t4c behave by the book (CF has a base
> date of 1899-12-30). With t4 and t4a you are first doing an
> addition and then doing inserting an integer in a datetime field.
> When you are inserting an integer in a datetime field the MS SQL
> server uses its own base date of 1900-01-01.
>
> With t4b you are not inserting an integer, but a time, and
> suddenly MS SQL Server uses the current date as the base date,
> instead of 1900-01-01 which is the documented base date. This
> might be an ODBC thing, but I suspect it is a MS SQL Server thing.
>
> > In fact all I am interested in are the hh:mm, I was wondering what other
> > people do when recording and calculating time...
>
> If I need to work with times, I don't use MS SQL Server but a
> database that has a TIME and an INTERVAL datatype.
>
> In your case I think it is best to convert your intervals (they
> are not times) to a number of seconds and store them in an
> integer field. That should be reasonable safe, but keep an open
> eye for DST issues.
>
> Jochem
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
