DataType Issue in SQL 2005!

Question

Post reply

DataType Issue in SQL 2005!

IN_Sandeep

SSCertifiable

Points: 6259
More actions
February 22, 2008 at 12:27 am

#186852

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.
--

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 2

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.

--

dragos_sv Default port Points: 1478 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 5

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.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Why do you want to store the date as a formatted string?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 7

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.

--

dragos_sv Default port Points: 1478 More actions · Answer 8

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 9

yes,:)

I got it,

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

Thanks..

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

I would strongly suggest that you store the date as a date time and do the conversion/formatting in your query/view.

Timestamp is used for row versioning, as the value changes whenever a row is updated, and the value is unique across the database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 11

IN_Sandeep

SSCertifiable

Points: 6259

February 22, 2008 at 5:27 am

#781049

Hi,

Thanks a lot.

Cheers!

Sandy.

--