New in SQL -need help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

New in SQL -need help

Author

Topic

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 12:24:32

Please help to remove (-)
The EM_TERMINATION_DATE column have records with no data.
I am using the following below script to format the dates.
The problem is : the format with (-) between yyyy-mm-dd retrive NULL records with (-)( see the output.How to fix it?
EM_TERMINATION_DATE
2006-03-15
- -
- -
- -
- -

Here is my script:
select
cast(left(EM_TERMINATION_DATE, 4) as varchar(4))
+ '-' +
Cast(right(EM_TERMINATION_DATE, 4) as varchar(2))
+ '-' +
Cast(left(Right(EM_TERMINATION_DATE,2), 2) as varchar(2))as TERM_DT

FROM EMF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 13:02:45

Wrong forum, moving thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 13:43:07

Can you post how the original data from EM_TERMINATION_DATE looks?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-14 : 14:10:07

You could put a where clause that eliminates those records.

select
cast(left(EM_TERMINATION_DATE, 4) as varchar(4))
+ '-' +
Cast(right(EM_TERMINATION_DATE, 4) as varchar(2))
+ '-' +
Cast(left(Right(EM_TERMINATION_DATE,2), 2) as varchar(2))as TERM_DT

FROM EMF

Where EM_TERMINATION_DATE <> ''

This assumes you're using a character based date and you aren't storing nulls in that column. If you are using nulls, use this where clause

Where coalesce (EM_TERMINATION_DATE,'') <> ''

OR you could use this one

Where EM_TERMINATION_DATE is not null --- this is the one to use if you only have null when there is no value in the column.

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 14:15:51

or this:-

select
cast(NULLIF(left(EM_TERMINATION_DATE, 4),' ') + '-' as varchar(5))
+ 
Cast(NULLIF(left(right(EM_TERMINATION_DATE, 4),2),' ') + '-' as varchar(3))
+ 
Cast(NULLIF(Right(EM_TERMINATION_DATE,2),' ')  as varchar(2))as TERM_DT

FROM EMF

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 14:54:51

It helped. Thanks sooo much.

quote:
Originally posted by cat_jesus

You could put a where clause that eliminates those records.

select
cast(left(EM_TERMINATION_DATE, 4) as varchar(4))
+ '-' +
Cast(right(EM_TERMINATION_DATE, 4) as varchar(2))
+ '-' +
Cast(left(Right(EM_TERMINATION_DATE,2), 2) as varchar(2))as TERM_DT

FROM EMF

Where EM_TERMINATION_DATE <> ''

This assumes you're using a character based date and you aren't storing nulls in that column. If you are using nulls, use this where clause

Where coalesce (EM_TERMINATION_DATE,'') <> ''

OR you could use this one

Where EM_TERMINATION_DATE is not null --- this is the one to use if you only have null when there is no value in the column.

An infinite universe is the ultimate cartesian product.

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 14:56:34

Thank you very much for your help

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 15:07:27

Would you please help with the date format.
I need to return both values in the yyyy-mm-dd format

What I need to return: EM_HIRE_DATE or EM_REHIRE_DATE if exist(date on which emp started working)The Output column has one value formated but not another.

output:
HIRE_DT
20070627
1986-09-29

Here is my script:

SELECT em_hire_date, em_rehire_date,
CASE WHEN em_rehire_date <> '' THEN em_rehire_date ELSE

RIGHT(CAST(YEAR(EM_HIRE_DATE) AS varchar(4)), 4)+ '-' +
RIGHT(CAST(1000 + MONTH(EM_HIRE_DATE) AS varchar(5)), 2)
+ '-' + RIGHT(CAST(1000 + DAY(EM_HIRE_DATE) AS varchar(5)), 2) end as HIRE_DT

FROM EMF

quote:
Originally posted by cat_jesus

You could put a where clause that eliminates those records.

select
cast(left(EM_TERMINATION_DATE, 4) as varchar(4))
+ '-' +
Cast(right(EM_TERMINATION_DATE, 4) as varchar(2))
+ '-' +
Cast(left(Right(EM_TERMINATION_DATE,2), 2) as varchar(2))as TERM_DT

FROM EMF

Where EM_TERMINATION_DATE <> ''

This assumes you're using a character based date and you aren't storing nulls in that column. If you are using nulls, use this where clause

Where coalesce (EM_TERMINATION_DATE,'') <> ''

OR you could use this one

Where EM_TERMINATION_DATE is not null --- this is the one to use if you only have null when there is no value in the column.

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-14 : 15:09:14

lookup convert function in books on line. It's very good documentation. I refer to it all the time.

An infinite universe is the ultimate cartesian product.

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 17:58:44

Thanks a lot. The books online helped.

quote:
Originally posted by cat_jesus

lookup convert function in books on line. It's very good documentation. I refer to it all the time.

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources