>
> 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]
