Convert - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Convert

Author

Topic

OON
Starting Member

22 Posts

Posted - 2010-11-02 : 16:16:54

Hello,

Im trying to convert a datatype to the Date format 101 using the following;

CONVERT(CHAR(10),INDV_DOB,101)AS BIRTH_DATE

The original data in INDV_DOB are as follows:

(Sample Data)
INDV_DOB
20020502
20010723
20010723
20030705
19990227
19990227
20000805

However, after using the convert function the data returned is still in the same format as the sample provided above instead of the 101 format which should be something like:
11/23/1998.

Thanks for your help

OON
Starting Member

22 Posts

Posted - 2010-11-02 : 16:22:02

If it helps, here;s the actual query im using

SELECT
F.HRN AS 'MED_REC',
F.NDC_NO,
F.DISP_DT,
F.QTY,
F.DAYS_SUPPLY,
CONVERT(CHAR(10),INDV_DOB,101)AS BIRTH_DATE,
PROV,
PHYS_NAME,
NDC_SPECIALTY,
DEA_NO
--INTO ADHD_CLAIMS
FROM RX.PRESC_CLAIMS F
INNER JOIN c438155.modified_ADD_A AL ON F.NDC_NO=NDC_CD
INNER JOIN CM.MemberDemogHRNLookup LU ON F.HRN=LU.MemberMRN
INNER JOIN CM.KDINDV1V K ON LU.MemberHRN=K.INDV_HRN
LEFT OUTER JOIN RX.NDC_PHYS MD ON F.PRESCRIBER_NAME=MD.DEA_NO
WHERE (F.DISP_DT BETWEEN '2010-10-01' AND '2010-11-01' )
AND(K.INDV_DOB BETWEEN 19981101 AND 20051001)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 16:23:24

one way is


SELECT CONVERT(CHAR(10),CAST('20020502' AS DATETIME),101)AS BIRTH_DATE

05/02/2002

If you don't have the passion to help people, you have no passion

OON
Starting Member

22 Posts

Posted - 2010-11-02 : 16:27:41

Hello,

i did this

CONVERT(CHAR(10),CAST(K.INDV_DOB AS DATETIME),101)AS BIRTH_DATE,

using the column in the table and i get the following error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

Could this be issue with the column format?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-02 : 17:46:00

1. is your datatype for INDV_DOB be int
2. you might have dirty data in INDV_DOB field. Could there be NULLs also? basically you have a bad design here with INDV_DOB. Anyways try the following

SELECT  HowAreYouDate = 
	CASE 
	   WHEN ISDATE(INDV_DOB ) = 1 THEN 'I am fine and you'
	   ELSE 'I am not a date you sucka'
	END, *

from CM.KDINDV1V

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources