Conversion timestamp -> datetime

Conversion timestamp -> datetime

Post by Circle Innovatio » Thu, 15 Aug 1996 04:00:00



Hi.

I'm trying to do a

  select convert(datetime, last_changed_dt, 102)
  from my_table

The last_changed_dt column is defined as a timestamp,
and I'm trying to convert the timestamp to a datetime.

The select gives me the following error message:
  Domain error during explicit conversion of VARBINARY
  value '' to a DATETIME failed.

I have no blank ('') last_changed_dt rows in my table.

I've tried to change the datetime to a smalldatetime.
In this case I don't get error messages, but rows which all
contains "Jan 1 1900 4:16AM". Seems that I get an overflow
(or underflow :-) ) or something.


Thanks,
-Johnny Andersen

 
 
 

Conversion timestamp -> datetime

Post by mdchach » Thu, 15 Aug 1996 04:00:00



> Hi.

> I'm trying to do a

>   select convert(datetime, last_changed_dt, 102)
>   from my_table

> The last_changed_dt column is defined as a timestamp,
> and I'm trying to convert the timestamp to a datetime.

> The select gives me the following error message:
>   Domain error during explicit conversion of VARBINARY
>   value '' to a DATETIME failed.

> I have no blank ('') last_changed_dt rows in my table.

> I've tried to change the datetime to a smalldatetime.
> In this case I don't get error messages, but rows which all
> contains "Jan 1 1900 4:16AM". Seems that I get an overflow
> (or underflow :-) ) or something.


> Thanks,
> -Johnny Andersen

Sorry, what you're trying to do can't be done.
timestamp is not directly related to the date/time
that it was created.

Mike

 
 
 

Conversion timestamp -> datetime

Post by Vadiraja Bhat » Sat, 17 Aug 1996 04:00:00



> Hi.

> I'm trying to do a

>   select convert(datetime, last_changed_dt, 102)
>   from my_table

> The last_changed_dt column is defined as a timestamp,
> and I'm trying to convert the timestamp to a datetime.

'timestamp' is a SQL server supplied, user defined datatype of the
type varbinary(8). This is not SQL standard 'timestamp' datatype.
The Transact SQL timestamp datatype does not hold the date time
information. This is mainly used for the purpose of 'Browsing' in
Client-Library application. Please look at SQL server ref manual 1
under 'timestamp' datatype.

Vadi

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Vadiraja Bhatt                                  ph: 510-922-3177
DSE                                      
Sybase, Emeryville
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

 
 
 

Conversion timestamp -> datetime

Post by J.Masino/T.Lars » Sun, 18 Aug 1996 04:00:00


: I'm trying to do a
:
:   select convert(datetime, last_changed_dt, 102)
:   from my_table
:
: The last_changed_dt column is defined as a timestamp,
: and I'm trying to convert the timestamp to a datetime.
:
: The select gives me the following error message:
:   Domain error during explicit conversion of VARBINARY
:   value '' to a DATETIME failed.
:

The problem is you cannot do what you want to do.  Unforuntately,
the name "timestamp" is a misnomer.  The data in a timestamp column
has nothing to do with date or time information.  It is simply a hex
number that is used to see if a value has changed since it was retrieved.
You can read about this in the Open Client documentation regarding
"for browse mode".  If you need date and/or time info, then you need to add
a column to your table that is of type "datetime".

I think every one of us has tried to do this same thing.  I REALLY wish
they had named it something other than timestamp.

                                Hope this helps
                                Teresa Larson

     ____________________________________________________________________
    /  Teresa A. Larson                   ISUG Electronic Media Chair   /
   /  Bell Atlantic                            Voice: (301) 282-0051   /
  /  13100 Columbia Pike, A-3-3                Fax:   (301) 282-9416  /

/___________________________________________________________________/
                        #include <std_disclaimer>

 
 
 

Conversion timestamp -> datetime

Post by Bsla » Wed, 28 Aug 1996 04:00:00


: I'm trying to do a
:
:   select convert(datetime, last_changed_dt, 102)
:   from my_table
:
: The last_changed_dt column is defined as a timestamp,
: and I'm trying to convert the timestamp to a datetime.
:
: The select gives me the following error message:
:   Domain error during explicit conversion of VARBINARY
:   value '' to a DATETIME failed.
:

As the other responders said, it's poorly named.  It's not a timestamp at
all, it's a unique automatically generated sequence number.

As long as we're on the subject of bad naming how about "text" types.
This is the worst kind of misnaming, ie. using a name which is commonly
used for something else (ie varchar strings).

Or how about "temp" tables.  Do you mean a transient table you just
created in your local database (ie. a normal table), or do you mean a
Sybase "temp" table.  Maybe "private" would have been a better name (only
you can see your table).

Um, sorry.  I was just in the mood to whine.  Most big products have these
pimples.  Such is life.

Ben Slade   Reston, VA, USA

"We'll meet again.  Don't know where.  Don't know when..."
The song played to exploding H-bombs at the end of Dr Strangelove

 
 
 

1. timestamp to datetime conversion

If you mean the timestamp SQL Server datatype, it's a varbinary(8) that has no
actual date or time information in it. It's just an ever-increasing,
guaranteed unique (within the scope of the database) value which is filled
when you insert a row and updated if you update the row.

I tripped over this one too. I _really_ wanted a date and time instead of a
meaningless varbinary. You might try a trigger that updates a datetime or
smalldatetime field with getdate() if you want a row to have its date and time
of last modification. That's my simpleminded solution.

Mark J. Howell

(pretty boring signature)

2. Raptor Firewall and Oracle

3. Need help with implicit varchar -> datetime conversion

4. xp_sendmail and limits in Outlooks To:, Cc:, and Bcc: fields

5. timestamp use ; timestamp conversions

6. ALTER to add Index

7. Datetime vs Timestamp problem

8. Sybase SQL Anywhere - problems !

9. get datetime from timestamp

10. Timestamp or datetime?

11. SQL datetime timestamp pb/bug ?

12. DATETIME to TIMESTAMP ?

13. Difference between timestamp and datetime in SQL 7.0?