Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to check whether in the given column we have values in date format Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 2:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
Hi,
I have a table like the one mentioned below.

Eid Variable
1 na
2 none
3 4/18/2013
4 18/2013
5 2013
6 9999999


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
Post #1443494
Posted Wednesday, April 17, 2013 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1443500
Posted Wednesday, April 17, 2013 6:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443559
Posted Wednesday, April 17, 2013 6:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
manibad (4/17/2013)
Hi,
I have a table like the one mentioned below.

Eid	Variable
1 na
2 none
3 4/18/2013
4 18/2013
5 2013
6 9999999


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443560
Posted Thursday, April 18, 2013 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,035, Visits: 11,772
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.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1443718
Posted Thursday, April 18, 2013 7:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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.
Post #1443843
Posted Thursday, April 18, 2013 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,035, Visits: 11,772
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1443859
Posted Friday, April 19, 2013 4:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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.
Post #1444636
Posted Friday, April 19, 2013 4:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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.
Post #1444642
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse