0

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
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
AnumR
  • 85
  • 1
  • 12

2 Answers2

0

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

xcvd
  • 666
  • 1
  • 5
  • 13
0
  IF (@LeaseCancelProcessedDate = '')    
    BEGIN    
      SET @LeaseCancelProcessedDate = NULL    
    END

Here you can use this syntax, in this @LeaseCancelProcessedDate is my Variable name, which I use to set value for my column of type DateTime. Initially, I was also facing the same issue but I resolve it using this small trick.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Chandra Shekhar Azad
  • 1
  • 1