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

Date Format Query Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 7:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518
Dear All,

I am reading a flatfile on which few columns are of the type date.

Those columns contains valid date format for most of the rows : mm-dd-yyyy hh:nn:ss

However some of the rows contain various string to show that the information is not available - example "NA" - "[Not Available]" - "[Access Denied]" and so on...

I am using an derived column component to typecase the date formatted string into DT_DBTIMESTAMP and want to reject the records which is not of the form -- that is arbitary strings.

I am using the logic :

Check if the column contains ":" if yes - then it seems to be a valid date of the format mm-dd-yyyy hh:nn:ss -- else consider this to be a NULL date.


LEN([Password Last Set Time]) == 0 ? NULL(DT_DBTIMESTAMP) : ((FINDSTRING([Password Last Set Time],":",1) == 0) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)([Password Last Set Time]))


Please let me know if you feel this is the correct approach - or advise accordingly.

Your help is much appreciated.


--
Sabya
Post #806467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse