Re: Date and Time

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 19:06:39 -0400

Again, datetimes are not stored with any format. They are stored as integer
pairs. The first integer contains the number of days since the seed date.
The second contains the number of milliseconds since midnight. There is
absolutely no point to trying to store a datetime with any format. Formats
must be applied when displaying the dates retrieved from the column.

For more information, check out the BOL article, Using Date and Time Data.

Bob Barrows

Paweł Janowski wrote:
> set dateformat dmy
>
> this force DD/MM/YYYY
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:um1zQ67eEHA.2560@TK2MSFTNGP09.phx.gbl...
>> Pawel Janowski wrote:
>>> "dave" <anonymous@discussions.microsoft.com> wrote in message
>>> news:10cf01c47b47$c67dbc80$a401280a@phx.gbl...
>>>> Hello
>>>> I have one field Dt_Time in sql server 2000.
>>>> I want to insert current Date (if possible then
>>>> DD/MM/YYYY)and Time in that field..What should be the SQL
>>>> for that?
>>>> Any help would be appreciated..
>>>> Thanx
>>>> Dave
>>>
>>> (BOL Bookz online) CAST and CONVERT
>>> in MSSQL insert directive :
>>> set dateformat dmy
>>> print convert(char(10),getdate(),103)
>>>
>>>
>>> Pawel Janowski
>>> www.sunrise-tm.com
>>> www.koluszki.net
>>
>> Why bring up CAST and CONVERT? The 103 style will be ignored when
>> the date is stored in the table. A simple:
>>
>> UPDATE tablename
>> SET Dt_Time = GETDATE()
>> WHERE ...
>>
>> or
>>
>> INSERT INTO tablename (Dt_Time, <list of other columns>)
>> VALUES (GETDATE(),<list of other values>)
>>
>> will suffice.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM" 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: distinct and yet duplicates
    ... What is unique about DateTimes is ... the integer portion of the number is the count of days since December 30, ... in short date format, but where the underlying numbers are quite different. ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: Date confusion
    ... > program will work with the date format mm/dd/yyyy and other times it ... > is dd/mm/yyyy and sometimes it will work with both. ... > program the application will work with the mm/dd/yyyy format and some ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)
  • Re: distinct and yet duplicates
    ... What is unique about DateTimes is ... the integer portion of the number is the count of days since December 30, ... in short date format, but where the underlying numbers are quite different. ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: Problem with ASP/Access DB
    ... I'm using the following SQL Statementbut i won't work. ... use the YYYY-MM-DD format. ... Personally, I prefer using stored procedures, or saved parameter queries ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: How do I add times beyond 24 hours in Access?
    ... I do not know which format to choose when setting ... Access (actually no database that I know of) has such a type of field. ... The usual practice is to store the smallest interval you care about as Integers ... Another way to go is to store start and end DateTimes and then use DateDiffto ...
    (microsoft.public.access.gettingstarted)