Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I am using one column in database which uses format datetime...rightnow one stored procedure updates datetime column in database with some of the rows have values of some date..which is most of the time like in datetiome format and same value ...something like 1900-01-01 00:00:00...something like that i am exactly not sure...but instead that value..i want each value replaced with just"-" instead that date now...but as that column has datetime format ...i am unable to store "-" in that value ...sql shows error..that "-" cant be stored in datetime format..is there any way i can able to do that?
Posted 15-Jan-13 18:35pm
jstu266310
Comments
Sergey Alexandrovich Kryukov at 16-Jan-13 1:05am
   
Why? You are trying to work with string representation of data instead of data itself, it's so hopeless...
—SA
jstu266 at 16-Jan-13 1:26am
   
actually..that stored procedure populates some of the date with 1900-01-01 00:00:00,this field ...now due to this our user are getting confused..now according to them they wanted to see '-' instead of the 1900-01-01 00:00:00..so they can knot have confusion..is there any way i can do that..its very urgent...I just wanted to replace that specific date with some "-" or just blanck space if possible...i am thinking but nothing is working out...
Milind_T at 16-Jan-13 3:58am
   
does the solution below helps?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Actually you have to keep value as NULL and check in your front end if the value is NULL don't show anything.
If for some reason you don't want to allow NULL in the column, it will save 01-01-1900 and then you check in your front-end if the value is 01-01-1900, don't show value.
 
Alternatively, in your stored procedure you can set like
 
select column1, column2, 
case when convert(varchar(10),yourDatecolumn,101) = '01-01-1900'
                then '-' 
                else yourDateColumn end)          as modifiedDate
from yourtable
 
I don;t have SQL access now, so correct the sytax before using.
Hoep that helps. If it does, mark it as answer/upvote.
 
Milind
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Can you specify the reason?
 
You can use following approach: store neither concrete date, or keep field value as NULL (column should be nullable).
On your client code (web frontend, or application) display fields that have value of null as '-'
  Permalink  
Comments
jstu266 at 16-Jan-13 18:58pm
   
Thanks a lot..I tried 1st one and it worked...However second solution below was not working ..i tried for 2nd for almost 3- 4 hours and then quit..and 1st one worked immeiately...Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 450
1 OriginalGriff 403
2 CPallini 189
3 Sergey Alexandrovich Kryukov 189
4 CHill60 180
0 OriginalGriff 6,092
1 Sergey Alexandrovich Kryukov 4,958
2 Maciej Los 3,269
3 Peter Leow 3,129
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100