| Author |
Topic |
|
MJPFitz
Starting Member
6 Posts |
 Posted - 2010-07-22 : 11:56:52
|
| Hi, hope someone can help, I have been asked to list employees by company and then by DOB. I can get a good list, but when I convert the datetime it errors out. This is because there are NULL values. Here is the statement, any work around? I would like the date in MM/DD/YYYY format.Select person.last_name + ', ' + person.first_name AS 'Employee', convert(DateTime,101)person.birth_date AS 'DOB', emp.company_code AS 'Company'FROM emp INNER JOIN person ON emp.person_id = person.person_id WHERE emp.hr_status = 'F' OR emp.hr_status = 'P' ORDER BY emp.company_code, convert(varchar(12), person.birth_date)MJPFitz |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 12:16:59
|
convert(DateTime,person.birth_date, 101) AS 'DOB',
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
MJPFitz
Starting Member
6 Posts |
Posted - 2010-07-22 : 12:33:22
|
| Thanks webfred, that has helped. Is there a way to not show the OO:OO:OO? And is there a way to short by month, not the year?MJPFitz |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-07-22 : 12:34:44
|
| Use a different dateformat from 101. See BOL for examples. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 12:46:09
|
Which datatype is DOB in the table and how does an example without converting it look like?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
MJPFitz
Starting Member
6 Posts |
Posted - 2010-07-22 : 12:58:26
|
| Well, it looks like this ...Employee DOB CompanyMcq, M 1942-04-29 00:00:00.000 CAYDa, M 1942-07-05 00:00:00.000 CENTor, 1943-03-17 00:00:00.000 PRKSn Jr., E 1945-07-07 00:00:00.000 PRKMJPFitz |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 12:59:43
|
datatype? is it datatime or varchar?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
MJPFitz
Starting Member
6 Posts |
Posted - 2010-07-22 : 13:02:00
|
| BTW and Thanks AndrewMurphy, I did change the type (101) but to no difference. I am thinking it is a format of the table/cell.MJPFitz |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 13:05:58
|
if varcharSelect person.last_name + ', ' + person.first_name AS 'Employee',left(person.birth_date,10) AS 'DOB',emp.company_code AS 'Company'...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
MJPFitz
Starting Member
6 Posts |
Posted - 2010-07-22 : 13:06:13
|
| It is datetime.MJPFitz |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 13:09:14
|
thenconvert(varchar(10),person.birth_date,101) as DOB,
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
MJPFitz
Starting Member
6 Posts |
Posted - 2010-07-22 : 13:17:51
|
Excellent, that did it, thank you very much.  MJPFitz |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-22 : 15:43:42
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Trying to get emp list shorted by DOB