Help with the date format - 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

Help with the date format

Author

Topic

dbacon
Starting Member

7 Posts

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

I need to return both values in the yyyy-mm-dd format

Here is I need to return: EM_HIRE_DATE or EM_REHIRE_DATE if exist(date on which emp started working).The Output returned only 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

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-05-14 : 15:21:14

Not sure which date format that you are asking for so I will give you both:


declare @date datetime 
set @date = getdate()
select [yyy-mm-dd]=convert(varchar, @date, 23), [yyymmdd]=convert(varchar, @date, 112)

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-14 : 15:21:46

check out books on line... it come with SQL Server. Do a search under t-sql for "convert".

It's a great resource and will give you a much faster answer.

An infinite universe is the ultimate cartesian product.

dbacon
Starting Member

7 Posts

Posted - 2008-05-14 : 18:07:37

I got it from the books online. Thanks a lot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-17 : 09:10:08

1 Always use proper DATETIME datatype to store dates
2 Let front end application do the formation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources