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 |
|
dfidwano
Starting Member
2 Posts |
 Posted - 2012-04-18 : 18:13:45
|
| I have a view that uses a simple select statement and one of the columns I'm returning contains a varchar value that is more than I need in the view. Example:Source value: 1/15/2012 12:00:00.000 AMRequired in view: 1/15/2012Essentially I want to chop off everything from the space on and only show the date but the problem is if I specify a character length or position that will only work for certain date values. I.E. if set at 8 characters then when the date is 12/15/2012 it returns only 12/15/20 but if I set it to 10 characters then for those rows that have 3/5/2012 will show as 3/5/2012 1 grabbing the space and part of the time.Please note this is not my database and I am not in a position to change the fact that the application is storing a date value with a timestamp as a varchar data type.I am also trying to avoid creating any temp tables or complex functions to pull this off.Thanks for any advice you can provide.Dwane |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-18 : 18:51:32
|
| select CONVERT(varchar(10),'1/15/2012 12:00:00.000 AM' , 101)select CONVERT(varchar(10),'12/15/2012 12:00:00.000 AM' , 101)<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
dfidwano
Starting Member
2 Posts |
Posted - 2012-04-18 : 22:33:25
|
| So I played around some more and I found something that seems to be working for the moment.select CONVERT(varchar, CONVERT(datetime, _DelDate, 101), 101) from _ProdDevAt first I tried converting the source varchar to datetime and used 101 as my style but that still would show the time as 00:00:00.000. But when I added the convert back to varchar with the 101 style again it worked. Hope I didn't open a can of worms but so far the 100,000+ rows seem to come back in just a few seconds so I have not seen any performance issues as of yet.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:20:58
|
| i cant understand how timepart with 00:00:00.000 will cause an issue for you. By making it as varchar, you'll have issue if you're using this converted varchar date value in future for manipulations like sorting. I would have left timepart as it as and preserved the datatype. The time part needs to be taken care of only in display which will handled at front end application using formatting functions.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Select partial varchar using delimiter