DateTime Fields 
Author Message
 DateTime Fields

Hi,
I believe this is a simple question.

I have a large table (76 fields, 75 floats, 1 datetime)
It has 86400 records each day. (# secs/day)
I archive it to Excel via DTS (works fine).

Implementation:  Before pumping to Excel, I split the
table up into two tables (AM and PM) since Excel can't
handle more than ~65000 records per sheet.

Problem:  SQL Server is storing the datetime field in 12
hour format and I believe a 24 hour format would be easier
to query in a Stored Proc.

How To:  Is it possible to force my table to store the
datetime field in 24 hour format?

Thanks in Advance,
Ben

Why not just select all records <= mm/dd/yyyy 11:59:59 am
into AM table and the rest into PM table, you ask?

I've tried, but I'm doing something wrong and 24 hour
format would 1) Be easier to query on and 2) Excel Sorts
on this field automatically and is putting my am's and
pm's together...This would kill two in one!

Ex:
12:00:00 am
12:00:00 pm
12:00:01 am
12:00:01 pm
...
Weird.



Tue, 20 Jul 2004 21:48:02 GMT
 DateTime Fields

The database stores datetime values in an internal format.  The display
of a datetime value is done by whatever client application you use to
access the data.  If your client program does not provide a way to
format datetime values, you can use CONVERT to display a datetime value
as a string using one of the supported formats.    More info is
available in BOL and Help.
Quote:

> Hi,
> I believe this is a simple question.

> I have a large table (76 fields, 75 floats, 1 datetime)
> It has 86400 records each day. (# secs/day)
> I archive it to Excel via DTS (works fine).

> Implementation:  Before pumping to Excel, I split the
> table up into two tables (AM and PM) since Excel can't
> handle more than ~65000 records per sheet.

> Problem:  SQL Server is storing the datetime field in 12
> hour format and I believe a 24 hour format would be easier
> to query in a Stored Proc.

> How To:  Is it possible to force my table to store the
> datetime field in 24 hour format?

> Thanks in Advance,
> Ben

> Why not just select all records <= mm/dd/yyyy 11:59:59 am
> into AM table and the rest into PM table, you ask?

> I've tried, but I'm doing something wrong and 24 hour
> format would 1) Be easier to query on and 2) Excel Sorts
> on this field automatically and is putting my am's and
> pm's together...This would kill two in one!

> Ex:
> 12:00:00 am
> 12:00:00 pm
> 12:00:01 am
> 12:00:01 pm
> ...
> Weird.



Tue, 20 Jul 2004 21:59:37 GMT
 DateTime Fields
Look at the Convert function in Books online.  Check codes 20 or 21.

TIG

Quote:
> Hi,
> I believe this is a simple question.

> I have a large table (76 fields, 75 floats, 1 datetime)
> It has 86400 records each day. (# secs/day)
> I archive it to Excel via DTS (works fine).

> Implementation:  Before pumping to Excel, I split the
> table up into two tables (AM and PM) since Excel can't
> handle more than ~65000 records per sheet.

> Problem:  SQL Server is storing the datetime field in 12
> hour format and I believe a 24 hour format would be easier
> to query in a Stored Proc.

> How To:  Is it possible to force my table to store the
> datetime field in 24 hour format?

> Thanks in Advance,
> Ben

> Why not just select all records <= mm/dd/yyyy 11:59:59 am
> into AM table and the rest into PM table, you ask?

> I've tried, but I'm doing something wrong and 24 hour
> format would 1) Be easier to query on and 2) Excel Sorts
> on this field automatically and is putting my am's and
> pm's together...This would kill two in one!

> Ex:
> 12:00:00 am
> 12:00:00 pm
> 12:00:01 am
> 12:00:01 pm
> ...
> Weird.



Tue, 20 Jul 2004 22:05:58 GMT
 DateTime Fields
Here's a good article explaining more about the Datetime format - briefly
and informative.

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=9723

Good luck,
Jeppe


Quote:
> Hi,
> I believe this is a simple question.

> I have a large table (76 fields, 75 floats, 1 datetime)
> It has 86400 records each day. (# secs/day)
> I archive it to Excel via DTS (works fine).

> Implementation:  Before pumping to Excel, I split the
> table up into two tables (AM and PM) since Excel can't
> handle more than ~65000 records per sheet.

> Problem:  SQL Server is storing the datetime field in 12
> hour format and I believe a 24 hour format would be easier
> to query in a Stored Proc.

> How To:  Is it possible to force my table to store the
> datetime field in 24 hour format?

> Thanks in Advance,
> Ben

> Why not just select all records <= mm/dd/yyyy 11:59:59 am
> into AM table and the rest into PM table, you ask?

> I've tried, but I'm doing something wrong and 24 hour
> format would 1) Be easier to query on and 2) Excel Sorts
> on this field automatically and is putting my am's and
> pm's together...This would kill two in one!

> Ex:
> 12:00:00 am
> 12:00:00 pm
> 12:00:01 am
> 12:00:01 pm
> ...
> Weird.



Tue, 20 Jul 2004 22:52:27 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. test a datetime value against 2 datetime fields

2. Treating datetime fields as separate date and time fields

3. Query datetime-field by date when field contains date + time

4. Concatenating datetime field and int field to make a date time

5. FoxPro Date Field to SQL datetime field

6. MAX on datetime field

7. Deleting records from a table using DateTime fields

8. BCP and ASCII datetime fields

9. Calculated DateTime field

10. Delphi 3.01 and DateTime Fields.

11. Grouping by datetime field

12. SQL Server CE DateTime Field Defect !?!?


 
Powered by phpBB® Forum Software