I want to store just "-" in datetime column in sql server 2008

Question

1.00/5 (1 vote)

See more:

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

jstu266

Add a Solution

Comments

Sergey Alexandrovich Kryukov 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 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...

MT_ 16-Jan-13 3:58am

does the solution below helps?

2 solutions

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

SQL

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

Posted 15-Jan-13 20:25pm

MT_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vyacheslav Voronenko · Accepted Answer · 2013-01-15T20:07:00

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 '-'

Posted 15-Jan-13 20:07pm

Vyacheslav Voronenko

Comments

jstu266 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