how to check whether in the given column we have values in date format

Question

Post reply

how to check whether in the given column we have values in date format

manibad

SSCommitted

Points: 1556
More actions
April 17, 2013 at 2:44 pm

#273169

Hi,
I have a table like the one mentioned below.
EidVariable
1na
2none
34/18/2013
418/2013
52013
69999999
I need to check for eid where variable column has data in the format mm/dd/yyyy and if suppose its not there i should neglect that particular eid.
i shloud do this validation in SSIS.
In informatica i can use IS_Date function but do we have any fucntions similar to is_date in SSIS?
Please help me in this.
Thanks in advance
Note:Column names are just mentioned for instances

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there is an ISDATE function in SQL as well;

here's an example base don your post:

/*

Eid Variable Date? Converted?

---- --------- ----------- -----------------------

1 na 0 NULL

2 none 0 NULL

3 4/18/2013 1 2013-04-18 00:00:00.000

4 18/2013 0 NULL

5 2013 1 2013-01-01 00:00:00.000

6 9999999 0 NULL

*/

WITH MySampleData (Eid,Variable)

AS

(

SELECT '1','na' UNION ALL

SELECT '2','none' UNION ALL

SELECT '3','4/18/2013' UNION ALL

SELECT '4','18/2013' UNION ALL

SELECT '5','2013' UNION ALL

SELECT '6','9999999'

)

SELECT

MySampleData.*,

ISDATE(Variable) AS [Date?],

CASE

WHEN ISDATE(Variable) = 1

THEN CONVERT(datetime,Variable)

ELSE NULL

END As [Converted?]

FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Lowell (4/17/2013)
there is an ISDATE function in SQL as well;
here's an example base don your post:
/*
/*
Eid Variable Date? Converted?
---- --------- ----------- -----------------------
1 na 0 NULL
2 none 0 NULL
3 4/18/2013 1 2013-04-18 00:00:00.000
4 18/2013 0 NULL
5 2013 1 2013-01-01 00:00:00.000
6 9999999 0 NULL
*/
WITH MySampleData (Eid,Variable)
AS
(
SELECT '1','na' UNION ALL
SELECT '2','none' UNION ALL
SELECT '3','4/18/2013' UNION ALL
SELECT '4','18/2013' UNION ALL
SELECT '5','2013' UNION ALL
SELECT '6','9999999'
)
SELECT
MySampleData.*,
ISDATE(Variable) AS [Date?],
CASE
WHEN ISDATE(Variable) = 1
THEN CONVERT(datetime,Variable)
ELSE NULL
END As [Converted?]
FROM MySampleData

Although it's not a flaw, you've just demonstrated a "problem" with IsDate that's similar to people thinking that IsNumeric means "IsAllDigits". It'll take a whole lot more than just mm/dd/yyyy as a date. The OP's requirement is to capture only those things that look like mm/dd/yyyy as well as being a valid data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

manibad (4/17/2013)
Hi,
I have a table like the one mentioned below.
EidVariable
1na
2none
34/18/2013
418/2013
52013
69999999
I need to check for eid where variable column has data in the format mm/dd/yyyy and if suppose its not there i should neglect that particular eid.
i shloud do this validation in SSIS.
In informatica i can use IS_Date function but do we have any fucntions similar to is_date in SSIS?
Please help me in this.
Thanks in advance
Note:Column names are just mentioned for instances

I'm sure it can be done in SSIS but any "is date" function my have problems depending on what it thinks a date is. To get around that, you may have to use both ISDATE and a pattern match to guarantee the correct format.

Borrowing heavily on Lowell's test data (which you should supply in this format in the future), here's how to do it in T-SQL. I'd do it for you in SSIS but I don't even know how to spell it correctly. 😛

WITH MySampleData (Eid,Variable)

AS

(

SELECT '1','na' UNION ALL

SELECT '2','none' UNION ALL

SELECT '3','4/18/2013' UNION ALL

SELECT '4','18/2013' UNION ALL

SELECT '5','2013' UNION ALL

SELECT '6','9999999'

)

SELECT Eid,Variable

FROM MySampleData

WHERE 1 = CASE

WHEN ISDATE(Variable) = 1

AND Variable LIKE '[0-9]%/[0-9]%/[0-9][0-9][0-9][0-9]'

THEN 1

ELSE 0

END

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

I don't think that there is an IsDate function in SSIS. If you don't mind doing a bit of coding, you could create a script component and do a Regex test on the column - here's a link to a regular expression which validates all dates except for non-leap-year 29 Februarys.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manibad SSCommitted Points: 1556 More actions · Answer 5

thanks for everyone who have replied..

Actually i am not in a position to work on SQl or Script task and code the data to achieve my destiny..i can do it only in SSIS.Is there any possiblity to achieve it by SSIS and specfically using dervied column.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

manibad (4/18/2013)
thanks for everyone who have replied..
Actually i am not in a position to work on SQl or Script task and code the data to achieve my destiny..i can do it only in SSIS.Is there any possiblity to achieve it by SSIS and specfically using dervied column.

I mentioned a Script Component, not a Script Task.

Regardless of that, both are 'in SSIS'.

If you do find a derived column solution, please post back, because I'm sure it will be useful to others.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manibad SSCommitted Points: 1556 More actions · Answer 7

I found a method to achieve my goal in derived column in SSIS.

the code is:

((SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,6,1)=="/")||

(SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

(SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,4,1)=="/")||

(SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||

(SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,6,1)=="-")||

(SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,5,1)=="-")||

(SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,4,1)=="-")||

(SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,5,1)=="-"))?

(DT_STR,8,1252)(REPLACE((DT_WSTR,10)(DT_DBDATE)Variable,"-","")):REPLICATE(" ",8)

but here in this code i have a glitch..

the glitch is if suppose the data is like xx/yy/mmmm then it will throw a error..and i dunno how to rectify this glltch.for now we haven't stored such sort of data but still i want to rectify this glitch so can anyone please help me.

Thanks in advance.

manibad SSCommitted Points: 1556 More actions · Answer 8

manibad (4/19/2013)
I found a method to achieve my goal in derived column in SSIS.
the code is:
((SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,6,1)=="/")||
(SUBSTRING(Variable,3,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||
(SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,4,1)=="/")||
(SUBSTRING(Variable,2,1)=="/"&&SUBSTRING(Variable,5,1)=="/")||
(SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,6,1)=="-")||
(SUBSTRING(Variable,3,1)=="-"&&SUBSTRING(Variable,5,1)=="-")||
(SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,4,1)=="-")||
(SUBSTRING(Variable,2,1)=="-"&&SUBSTRING(Variable,5,1)=="-"))?
(DT_STR,8,1252)(REPLACE((DT_WSTR,10)(DT_DBDATE)Variable,"-","")):REPLICATE(" ",8)
but here in this code i have a glitch..
the glitch is if suppose the data is like xx/yy/mmmm then it will throw a error..and i dunno how to rectify this glltch.for now we haven't stored such sort of data but still i want to rectify this glitch so can anyone please help me.
Thanks in advance.

The output of the code would be

variable=1/31/2013 o/p=20130131

variable=31/2013 o/p=<space>

Thanks.