Hi,
I am having probelm in decoding datetime from MS-SQL database

the db value is 1151996018 and the value is 7/4/2006 12:23:38pm

i want a query which decode this from database
i have struggled to get this much
Select Convert(nchar,CAST((timestamp/86400 +25568),datetime),101) from
table

but this gives me 07/04/2006

can anyone let me know how to query out without those 0's and about
time
if u can provide me query that would be great

Regards,
Monika

Good old Books Online:
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Look at the style parameter and the reference table that explains the values.


ML

---
http://milambda.blogspot.com/

You're using the wrong datatype to store your date/time value in. Date time
is stored in fields of type 'datetime'. 'timestamp' is used for things like
optimistic locking in MS sql.

If you would use a column of type datetime, the value is easily converted
into string format using the convert/cast operators as described in books
online.

i.e.

create table dbo.x (
  [id] int identity(1,1) not null,
  [adate] datetime not null default getdate(),
  constraint pkX primary key clustered ([id])
)

insert dbo.x( adate)
values( {ts '2006-08-24 13:57:00'})

select convert( varchar(36), adate, 106)
from dbo.x

result is:


------------------------------------
24 Aug 2006

(1 row(s) affected)


Look up your prefered format (just vary the 106 in the above example) in
books online, by searching for 'convert' in transact sql.

Good luck,

Ever growing transaction log, using simple recovery?
Help with efficiently storing a variety nested queries
EncryptByCert problem
The pains of XML programming
Simple trigger question