Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Date Format Query Expand / Collapse
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: Thursday, October 8, 2015 11:18 PM
Points: 346, Visits: 524
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.

Post #806467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse