| Author |
Topic |
|
slboytoy
Starting Member
30 Posts |
 Posted - 2005-06-15 : 17:51:10
|
| I have a datetime column that is together.7/1/2005 8:34:12 AMI'm going to split it into two columns, date and time.The date column is fine, but when I do the time, I get1/1/1900 8:34:12 AMIs there any way to get rid of the date? If I manually erase the date, just the time stays and that is good.! Except my table is over 2 million (I want to automate this). I've been trying for awhile with convert and stuff like that. Kind of trying me crazy with Cast and convert and all those things. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-15 : 18:13:13
|
| Here you go:SELECT CONVERT(varchar(50), GETDATE(), 101) AS Dt, CONVERT(varchar(50), GETDATE(), 108) AS TmTara |
 |
|
|
slboytoy
Starting Member
30 Posts |
Posted - 2005-06-16 : 08:24:30
|
| It does give me the correc time, but after you 'UPDATE' the column with the new time, it still says '1/1/1900 8:25:04 AM' I'm looking for a way to get rid of the 1/1/1900 part. I can manualy erase the date, but there has to be a way to do this automaticly, I'm not sure how. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-16 : 09:07:54
|
you do realize that bot date and time are stored in the column?that's one datatype not two. you can't "split" them.displaying them correctly is a presentation layer issue.Go with the flow & have fun! Else fight the flow  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-16 : 12:09:21
|
| The only way to split them in the database is to use varchar or char as the data type. You can't use datetime as Mladen mentioned.Tara |
|
|
|
slboytoy
Starting Member
30 Posts |
Posted - 2005-06-21 : 11:54:03
|
| Ok I changed my table to a varchar. Too bad there wasn't a time datatype. Thanks anyways. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-21 : 12:43:19
|
quote:
Originally posted by slboytoy
Ok I changed my table to a varchar. Too bad there wasn't a time datatype. Thanks anyways.
They were going to do this in SQL Server 2005, but they removed it.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-21 : 13:33:57
|
| No -- do not use a VARCHAR to store times. Use a datetime just like you have. Check out the post Brett linked to and the other one referenced in my weblog.Remember that a datetime AWLAYS stores BOTH a date and a time. Just because a datagrid doesn't display one or the other doesn't mean it is not there. Just like a decimal -- if it displays .34 it doesn't mean that there isn't an actual value of 0.34, if you know what I mean. And just like numbers, because parts of the datetime are always STORED does not mean they have to have values not equal to 0.Most presentation layers will not show the time if it is set to 00:00:00 (basically, midnight) and many will not show the date if the numeric value if that date is 0. But those values are still there. Remember that internal datetimes are stored as numeric values. You can see what happens if you say:declare @d datetimeset @d = 0select @d Breaking out dates from times is:GOOD if you are doing it for indexing and performanceBAD if you are doing it so things "look nice" or display properlyAnd the best thing is, if do store your dates in 1 column and your times in another, with both datetime datatypes, you can just add them up to get back to your original datetime values.- Jeff |
|
|
|
|
DateTime into Date and Time columns