[SOLVED] Avoid default date (Jan 1900) in sql server varchar column

Avoid default date (Jan 1900) in sql server varchar column

I have a experience table in my sql sever database having following columns:

exp_id int,
u_id int ,
exp_jobtitle varchar(100),
exp_company varchar(100),
exp_startdate date,
exp_enddate varchar(50),
exp_working varchar(50),
exp_description varchar(2000),

If I am currently working in that particular job then nothing is inserted in exp_enddate column but Currently Working in exp_working column.

And in the frontend I am showing the result like this:

If my job is ended May 2015 - June 2015 and the problem occurs when I am currently working

I want this output May 2015 - Currently Working And I'm getting output like this

May 2015 - Jan 1900Currently Working

Through this query :-

select right(convert(varchar(50), cast('exp_enddate' as date), 106), 8)

How can I avoid this default date or Is there any other way to get the desired output?

May 2015 - Currently Working

Solution

If you are dealing with an empty string for the end date then SQL Server will automatically treat this as 1/1/1990.

You can see this by running the below query:

SELECT CAST( '' AS DATE )

This returns 1900-01-01.

What you can do is check if the end date is empty, and if so, treat it as currently working, like this:

SELECT 
    CASE 
        WHEN exp_enddate = '' 
        THEN 'Currently Working' 
        ELSE RIGHT( CONVERT( VARCHAR(50), CAST( exp_enddate AS DATE ), 106 ), 8 )
    END

This will either return the end date in your provided format, or 'currently working'.