Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-07-03 : 11:50:53
|
| Hi,Is it not possible to specify datatypes for views? Creating a view from a table with a datetime column appears to translate the column to one with a varchar datatype.DrewDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-03 : 12:01:47
|
| How did you find that out?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-07-03 : 12:01:52
|
| If you used a CAST or CONVERT function on your DATETIME column in your view definition, your column will now have a VARCHAR data type and not the DATETIME data type anymore. Either try removing the CAST or CONVERT function, or do another CAST/CONVERT and change it back to DATETIME.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-07-03 : 12:16:08
|
quote:
Originally posted by harsh_athalye
How did you find that out?
The column is no longer puting the data in ascending order (incrementing times i.e. 10am, 11am, 12pm, 1pm...)I haven't used CAST or CONVERT. I have used RIGHT(Start_Time, 7) though to retrieve just the time part of the datetime data (1:00pm). If I create a view from this table the datatype is varchar.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-07-03 : 12:24:51
|
quote:
Originally posted by jsmith8858
Yes, the RIGHT() function returns a string, not a time! If you want just the time part of a column, you need to do some "date math" to return the time a "date zero" (1/1/1900).See the TimeOnly() function here:http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
Oh right. Cheers. I didn't think it returned a string, because the right function changes the format of a datetime entry, 1900-01-01 07:00:00.000, to 7:00PM.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-03 : 12:36:03
|
| Remember, there is no formatting in SQL -- only data. If you try to format anything, it becomes a string value. that's why you always return raw data from SQL statements and let your reports or front end clients do any formatting necessary. This applies to "time only" values, too -- technically, SQL is storing:1/1/1900 1:00 AMfor "1:00 AM", since it cannot just store a time value without a date as well. It is the job of the client or report to output that datetime value as just "1:00 AM" or whatever format it needs, but in order for the client or report to do so, it must receive a true "datetime" value, not a string, from the database.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
Specifying datatypes for views