Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DataType Issue in SQL 2005! Expand / Collapse
Author
Message
Posted Friday, February 22, 2008 12:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi All,

My Application is connecting to 3 type of database like : MS Sql Server 2005, Oracle 11i, MySQL.

I have a LoginLog Table, where I have 2 nos. of column as Login and Logout. In MySQl and Oracle the above column's datatype are Timestamp and its working fine.

I wants the same way in My sql server the datatype should be timestamp. its working fine. but when i am inserting the data the data is storing in binary format

ex : 0x00000000000007D3


like this, when i am also fetching the rows its also showing like this.

So my question is how can I store the exact format of timestamp as stored in Oracle and MySQL?

Ex : select current_timestamp.


Please help me.

Cheers!

Sandy.


--
Post #458939
Posted Friday, February 22, 2008 1:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
Despite its name, the timestamp datatype has nothing to do with times. It's a binary rowversion.

It you're wanting to store the date that the rows was inserted, then you need datetime with a default of getdate()



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #458949
Posted Friday, February 22, 2008 2:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Thanks,

But I need to store the date data in this format as like Oracle and MySQL

Ex: 2008-02-22 14:27:45.623


because the application is only changing the connection to the SQL,MySQL & Oracle as per required. The code is already build.

So How Can I handle that?
Any suggestion?

Cheers!

Sandy.


--
Post #458960
Posted Friday, February 22, 2008 2:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 798, Visits: 999
hi

if you must store the data in that format, use a column type char(23) with default value: convert (char(23), getdate(), 121)

hope i understood your requirement right and that the solution works for you

dragos
Post #458963
Posted Friday, February 22, 2008 2:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
Use a datetime. Format is a function of how you retrieve the data, not how you store it.

Look up Convert for a list of the formating options for datetimes.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #458964
Posted Friday, February 22, 2008 2:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi all,

I am following the same way what you are suggesting now.

Ex : select convert(varchar(30),getdate(),121)

But the important for me is storing rather than fetching the data.

Any other ways??

Cheers!

Sandy.


--
Post #458972
Posted Friday, February 22, 2008 2:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 42,844, Visits: 35,972
Why do you want to store the date as a formatted string?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #458973
Posted Friday, February 22, 2008 2:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Hi GilaMonster,

In my Web Application the code is written in such a way that this format of date will support for all type of database not only SQL Server.

otherwise I need to add extra codes in my application to convert the date manually,

I wants the data should support as global for all the database.

As far as search module concern, it is not going to become an issue for me. but Can I store in
2008-02-22 14:55:11.733
format by using datetime datatype or not?

Cheers!

Sandy.


--
Post #458975
Posted Friday, February 22, 2008 2:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 798, Visits: 999
Sandy,
my answer to "Can I store in 2008-02-22 14:55:11.733 format by using datetime datatype" is no, you cannot.

you would have to store that value as char()...

maybe it works if you use a base table with a datetime column and put a view on top of it from where your application will get the data. in the view, you can convert the datetime to a string formatted as required

dragos
Post #458978
Posted Friday, February 22, 2008 3:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
yes,:)

I got it,

Can you please tell me what is the use of Timestamp Datatype then???

Thanks..

Sandy.


--
Post #458987
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse