| Author |
Topic |
|
harshal_in
Aged Yak Warrior
633 Posts |
 Posted - 2003-08-08 : 01:37:42
|
| hi,What is the best way to save the time data,If I just want to save time in a column and get the results sorted according to the time .If the datatype of the column is datetime it saves 1/1/1900 and the time.regards,Harshal.The Judgement of the Judge is as good as the Judge. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-08 : 01:42:52
|
| What is the problem again?Use a smalldatetime field and don't worry about the yyyymmdd portion. Sorting will be unaffected.DavidM"SQL-3 is an abomination.." |
 |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-08-08 : 02:05:58
|
| ok thnxs.The Judgement of the Judge is as good as the Judge. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 02:36:52
|
| >> If the datatype of the column is datetime it saves 1/1/1900 and the time.Nope - it displays that when it formats for display using the default format if no date is entered (this being 0).The format of the disply is up to you to override.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-08 : 09:52:25
|
| Ok, so this has been bugging me...why doesn't sql server have data types ofdateTimewe all know aboutdatetimeand isn'ttimestampa minomer?That one took awhile, when I first came over from db2 back in the day...DB2 timestamp is really SQL Server datetime with precision of a 6 position microseconds...Are datatypes part or not part of ANSI standards...and on the subject of ANSI, what about SQL functionality like sorting...in DB2, UNION, DISTINCT, GROUP BY, ORDER BY....all sort the databut in SQL Server, only ORDER BY? WhyIsn't the functionallity of a SQL statement an ANSI thing, or not?Just Curious?OK....stick'em up...this is thread theft...Brett8-)SELECT POST=NewId() |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-08 : 10:23:33
|
| With seperate date and time data types, you'd really complicate the simplistic DATEADD and DATEDIFF functions. It's a trade off.Jay White{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-08 : 10:31:07
|
quote:
Originally posted by Page47
With seperate date and time data types, you'd really complicate the simplistic DATEADD and DATEDIFF functions. It's a trade off.Jay White{0}
OK, but is it ANSI? and by no means did I mean to imply the elimination of datetime (DB2's timestamp)...it's just, why does time have any meaning for things like DOB? Or Date for my 5:22 train home?And what about those sorts?Brett8-)SELECT POST=NewId() |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 10:31:22
|
| A datetime will hold a date or a time.timestamp will give the (time based) order in which actions occur (but I agree it's not a good name - especially when you have current_timestamp too).In v6.5 a group by did order the resultset - but why do that if it's not what is wanted.Don't think there's anything about sorting in the ansi standard (not 92 anyway).>> Are datatypes part or not part of ANSI standards...sort of - SQL defines distinct data types named by the following <key word>s:CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL,INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME,TIMESTAMP, and INTERVAL.>> Isn't the functionallity of a SQL statement an ANSI thing, or not?Sort of - some of them - a bit.If you stick to the ansi standard you would lose a lot of functionality and probably performance.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-08 : 11:38:37
|
quote:
Originally posted by nrcurrent_timestamp too...
Holy Sh_t...didn't know that...is it a db2 through back....and why have it?backward compatability?Since there is no difference...SELECT current_timestamp, Getdate()Or is there (why do I hear Arnold's footsteps...)Brett8-)SELECT POST=NewId() |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-08 : 12:45:58
|
| SQL Server's timestamp data type is not the same as the ANSI timestamp type, and it's going away. It will be called rowversion in later releases. |
|
|
|
|
time