Date comparison - 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 2005 Forums

Transact-SQL (2005)

Date comparison

Author

Topic

Trybbe
Starting Member

27 Posts

Posted - 2008-12-03 : 05:16:08

Hi! I need help

I have two columns which I need to compare.
Date of birth and NationalID both these columns are of varchar(15).

Date of birth = '20 Jul 1953'
NationalID = 5202255063080

What I'm trying to achieve is:- The first six digits of the national ID is the date of birth, therefore should be the same as data in the dateofbirth column however this is not the case in some instances as you can see above.

So I tried this query with noluck:

SELECT     Staff_Payroll.DateOfBirth, LEFT(Staff.NationalID, 6) AS NationalID
FROM         Staff_Payroll INNER JOIN
                      Staff ON Staff_Payroll.StaffNo = Staff.Staffno AND Staff_Payroll.Period = Staff.Period
Where Staff.Period = 200810
AND Staff.NationalID != ''
AND (ISDATE(LEFT(Staff.NationalID, 6)) = 1)
AND (Staff.CallName NOT BETWEEN '0' AND '9')
AND (Staff.CallName NOT IN ('Assessment', 'Fica', 'SACP Imaging', 'PPM', 'Teller', 'E-RM', 'NCCC')) 
AND (Staff.CallName NOT LIKE 'Train%') 
AND (Staff.CallName NOT LIKE 'Test%') 
AND (Staff.CallName NOT LIKE 'DCAR%') 
AND (Staff.CallName NOT LIKE 'Retail%') 
AND (Staff.Surname NOT LIKE 'Train%') 
AND (Staff.Surname NOT LIKE 'Returned%') 
AND (Staff.Surname NOT LIKE 'Silica%') 
AND (Staff.BranchID NOT LIKE 'FPB%') 
and Staff_Payroll.DateOfBirth != Left(Staff.NationalID,6)

Please assist.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 05:24:41

What format is the first six digits of NationalID?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 05:38:37

Seems like yymmdd

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 05:40:20

[code]DECLARE @Sample TABLE
(
DoB VARCHAR(20),
NationalID BIGINT
)

INSERT @Sample
SELECT '20 Jul 1953', 5202255063080

SELECT *,
CONVERT(CHAR(6), CAST(DoB AS DATETIME), 12),
LEFT(NationalID, 6)
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources