SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
manibad
manibad
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28147 Visits: 39945
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85951 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85951 Visits: 41091
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. :-P
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18435 Visits: 20427
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
manibad
manibad
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18435 Visits: 20427
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
manibad
manibad
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
manibad
manibad
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search