| Author |
Topic |
|
makwana.sonal
Starting Member
3 Posts |
 Posted - 2006-09-04 : 05:36:58
|
| hello,m new to sql server.can any one tell me is it possible to insert only time value in timestamp datatype.i have table which have datatype datetime.and i have to insert only time.i can't change the datatype datetime to any other one.pplz any one can suggest me the solution..Regards,Sonal. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-04 : 05:40:46
|
| No, TIMESTAMP and SMALLDATETIME/DATETIME always has date information. Learn about proper datatypes in Books Online.However, it is possible to store time only value. The trick is to remove date information (defaulting to date zero) such asselect dateadd(second, DATEDIFF(second, datediff(day, 0, getdate()), getdate()), 0)Peter LarssonHelsingborg, Sweden |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-04 : 05:50:21
|
quote:
it possible to insert only time value in timestamp datatype
timestamp datatype is not for storing time. From Books OnLinequote:
timestamptimestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.RemarksThe Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements
To get only time value inclusive of millisecondselect dateadd(day, -datediff(day, 0, getdate()), getdate())
KH |
|
|
|
makwana.sonal
Starting Member
3 Posts |
Posted - 2006-09-04 : 07:13:34
|
| my application is connected to the attandance machine and when data is fetched from machine,datbase contain only time value.so it is possible to insert only time value in sql server. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-04 : 07:19:09
|
no. data is always stored as date and time.there is no only date or only time datatypeGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
makwana.sonal
Starting Member
3 Posts |
Posted - 2006-09-04 : 07:27:55
|
| but when m downloading the data from my machine which is already manifacured,tranfrred only time value..it is like 12:12:12 pm..bt when m inserting data through my software application.it takes as 12/07/2006 12:12:12 pm..so plz any one give me solution...it's argent. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-09-04 : 07:40:19
|
| sorry but i have no idea what you're trying telling us...Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-04 : 07:41:34
|
| normally when we only have to save the Time, we choose SmallDateTime Datatype and while inserting the value, we Insert in the following format. "1900-01-01 15:30:00:00".Then while retriving the data we use the convert function, somthing like this.. Select convert(varchar(20),GetDate(),114)Chirag |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-04 : 07:45:06
|
| INSERT INTO YourTable SELECT CAST(CONVERT(VARCHAR, GETDATE(), 112) + ' ' + RemoteColumn AS DATETIME)Peter LarssonHelsingborg, Sweden |
|
|
|
|
about the timestamp