.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh
Post New Web Links

Testing for Valid Date or Null Value -- Using Conditional Split

Posted By:      Posted Date: October 31, 2010    Points: 0   Category :Sql Server
 

I have a Flat File Source with several columns of data that should be valid dates.  Sometimes however they may either be NULL or not valid dates.  I understand the expresstion ISNULL([ColumnName]) will test for the NULL condition.  What is the syntax to test if the value is a valid date?  It might be a string such as "baddate" or it could be a date that is too old.  Could you provide an example of an expression that will test for both of these conditions?

Thanks.

Steven

 




View Complete Post


More Related Resource Links

Using a CompareValidator to check input is a valid date

  
The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

Using Conditional Split data Transfer in SSIS 2008

  
This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Conversion from string to type 'Date' is not valid

  

Hi

Im receiving a strange error Error Message: Conversion from string "15/08/2010 22:21:35" to type 'Date' is not valid.

I know this is generally down to cultural date formats etc, however the reason this is strange is it just randomly occured twice in the last week, the website has been running since october and nothing has changed,

The error constantly occured until the IIS was reset and recompilled the site, its been running fine since, however im just trying to figure out what could cause such an error to occur.

I've narrowed it down to a line of code which is:

bktime = DReader.Item("Bktime").ToString

bktime = Year(CDate(bktime)) & "-" & fmt(Month(CDate(bktime)), 2) & "-" & fmt(Day(CDate(bktime)), 2)


Has anyone else encountered this randomly occuring and is there anything i can put in place to prevent this in future?


Cheers


Mike


Updating a List item field value in a SPD Workflow is setting Date fields to null

  
Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Not Null Date Default

  
I'm one of the people that try to avoid nulls when ever possible, usually using defaults.  This is especially important since my apps do a lot of data exchange with external entities often involving flat files.  A flat file can't distinguish between a null or an empty value so to ensure consistancy between data types in both systems I try to make sure every field has some value.  (E.g., If I have a null or empty string in a char field type a CSV flatfile shows ',,' for both.  The target system might interpret this as an empty string or a null; I have no direct control.  This could result in a null in one system and '' in another losing consistancy.  For charactor and number types this is rarely a problem if I communicate that there are no null fields in the data, but for dates it often becomes an issue. Is there a industry standard approach to managing "empty dates" so I can make date fields not null and empty? Thanks

Interesting error message with content organiser: Cannot resolve mailbox (null) to a valid user

  
Here is a site with content organiser site feature enabled, as well as the email integration feature. Emails are successfully delivered to the "Submitted Email Records" list, but is never routed to the Drop Off Library. (the content organiser web service works fine and the drop off library works as a custom send to destination and happily processes its rules. If I turn up the debug juice I get the error "Cannot resolve mailbox (null) to a valid user" for every item in any "Submitted Email Records" in the site collection. Area: Document managagement Category: Content organiser EventID: 8000 Message: Cannot resolve mailbox (null) to a valid user I have temporarily worked around the problem by email enabling the drop off library but it creates an intermittent race condition (similar to certain workflow and event receiver situations), so its not a real solution (and in fact I assume thats why the Submitted Email Records hidden list exists in the first place). I've run procmon, specifically focusing on OSWTIMER and nothing interesting shows up at all. The error message is intriguing and I can't really think of what it is trying to do. In troubleshooting I noted that this farm had configured inbound email but not outbound email. I have since rectified this and de/reactivated the content organiser features and reset the timer service and IIS, b

how to force null in date field - access 2007 ?

  
hi i have table that contain date field. how i can force null or empty value on this field ? thank's in advance

Using User Variables (ONLY) in SSIS Conditional Split

  
After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

ASP.Net / ODP.Net setting null date

  
Using VS2008, c# and ODP.Net(2.111.7.20)   In the following procedure  a_end_date  may or may not be null. If the input string is null, I tried to set  a_end_date to null. It is giving error.  The same result if I try to set it to dbNull.   How do I set null to a date field   Thanks Suresh   public string InsertOnlineApplication( string a_start_date,                                           string a_end_date,                                           string a_status,                                           string a_app_type) {              OracleGlobalization info = OracleGlobalization.GetClientInfo();            &nbs

error: null is not valid value for stream

  

Dim strm As System.IO.Stream = Me.GetType().Assembly.GetManifestResourceStream("fullbuttonface.bmp")

_buttonFace = New Bitmap(strm)

May be the  bitmap file is not part of the assembly, how do I put it in the assembly

Thanks


Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid.

  

I am getting the below error. Please see my code below and let me know what is wrong.

 

dim sDatetime as string = startdate & " " &starttime & "AM"

dim dt as Date = CDate(sDatetime)

I am getting the Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid error in this line. 


how to use split in iif conditional expression in ssrs 2008 r2

  

hi,

how can i use conditional iif combined with split?

i'm trying to get values from check boxes(from sharepoint custom list), the condition is like this :

if the value is null/blank, the return value would be "-"

if the value is not blank, the return value would be the value of the check box field that already splitted from the ";#" character

as you might know, values from check box is written along with ";#" character before and after the value itself.

i tried to use this expression:

=IIF(Fields!Action__For_User_Access_.Value Is Nothing," - ",(Split(Fields!Action__For_User_Access_.Value, ";#")(1)))

the split worked just fine, but the conditional iif didn't work.

can anybody help me?

i used that expression in the calculated field.

btw, i'm using report builder content type from ssrs 2008 r2

 

thx in advance.

 

-willy-


Conditional Split

  

 

I have a problem using conditional split,

I've got the raw txt data that i need to insert into sql database, having set the delimiter as ",", i encountered a problem;

Raw data example,

ID,Name,Job

00100,Harris,Student

00101,Andrew, Jackson, Nurse

 

Data inserted to SQL ended up to be...

ID Name Job
00100 Harris Student
00101 Andrew Jackson ,Nurse

But when i initiate the conditional

If it is inserted correctly to the SQL Database,

It should be... (Take note of "Jackson")

Conditional split?

  

Hi All,

I have a package which imports several files on a regular bases . For the import I use a staging table. Now I want to split that stagingtable into two other (existing) tables with keeping the relation ( third table??) . In each record there is a dealerID which has to be injected also in the other two tables (the splittables).

How can I use a conditional split to extract the right columns to the right table? I only see that i can enter a expression for each column, but in fact i only want to split the table by "fingerpointing" the right columns. is that possible? And how should i put the relation between them in 'relation' table?

 

I hope someone van help me,

 

Thanks in advance,

 

Peter

 


Value of 'null' is not valid for 'stream'.

  

hi all,

i've got problem with load image PNG

my code :

Me.PictureBox_PngSource.Image = Image.FromStream(System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream("SISOLUSI.logo.png"))

ty before


Problem inserting a null value into date column in SQL table

  

Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

Conditional Split Error

  

Hi Guys,

Source Files - String Datatype

Column0

 

H, 1, 353474, 06, 52, 01, 01, 2009

C, 4

I, MV6106, 1, 3.85, , 3.85, R,  , 

T, 65496, -0.35

T, 1, 20.00

H, 1, 353475, 06, 53, 01, 01, 2009

C, 4

Reference: Cashout_TX_353476

I, 30159, 1, 1.00, , 1.00, R,  , 

I, 458287, 1, 6.91, , 6.91, R,  , 

I, 394085, 1, 2.15, , 2.15, R,  , 

I, MV1293, 1, 2.80, , 2.80, R,  , 

I, 132700, 1, 6.54, , 6.54, R,  , 

T, 65496, -0.88

T, 8, 19.40

H, 1, 353476, 06, 53,

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend