Passing Null Date Value To Database

Oct 31, 2004

how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.

View 19 Replies


ADVERTISEMENT

Passing A Null Date Value To Report

Dec 6, 2007

Hi,

I have an ASP.Net page that needs to sometimes pass a null parameter value to the querystring. It bombes out with an error that a date cannot be "" and that i need to supply the date.

Has any1 been able to get this right? How does one pass a null date through the querystring?

Regards
Mike

View 1 Replies View Related

Form Passing NULL To Database Instead Of Variable

Apr 12, 2007

Not sure what i'm doing wrong here. it seems to be sending nulls or no value to database in places where i want it to send the @headline and @entryhere's the .aspx code: <%@ Page Language="c#"     MasterPageFile="~/MasterPage.master"     AutoEventWireup="true"     CodeFile="NewPost.aspx.cs"     Inherits="NewPost"     Title="Scribbler Insert" %>       <asp:Content ID="PageBody" runat="server"    ContentPlaceHolderID="PageBody" ><table border="0" width="700" >  <tr>    <td width="80" valign="top">      Subject:    </td>    <td width="620" valign="top">      <asp:TextBox ID="txtHeadline" runat="server"           Width="400px"/>      <asp:RequiredFieldValidator runat="server"        ID="RequiredFieldValidator1"        ControlToValidate="txtHeadline"        ErrorMessage="Headline is required"         Display="Dynamic" />    </td>  </tr>  <tr>    <td width="80" valign="top">      Text:    </td>    <td width="620" valign="top">      <asp:TextBox ID="txtEntryText" runat="server"           TextMode="MultiLine"          Height="250px"          Width="400px" />      <asp:RequiredFieldValidator runat="server"        ID="RequiredFieldValidator2"        ControlToValidate="txtEntryText"        ErrorMessage="Text is required"         Display="Dynamic" />    </td>  </tr></table><asp:Button ID="btnPost" runat="server"     Text="Post" OnClick="btnPost_Click" /><asp:Button ID="btnCancel" runat="server"     PostBackUrl="~/Admin/Admin.aspx"     Text="Cancel" />   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:scribblerdbConnectionString %>"            ProviderName="<%$ ConnectionStrings:scribblerdbConnectionString.ProviderName %>"    InsertCommand="INSERT INTO entries_tbl (Headline, entry, PostedBy, PostedDate, PostedTime) VALUES (@Headline, @Entry, 'SteveP', '2007-12-12', '12:44');" >         <InsertParameters>            <asp:ControlParameter Name="Headline"                ControlID="txtHeadline"                PropertyName="Text"                Type="String" />            <asp:ControlParameter Name="entry"                ControlID="txtEntryText"                PropertyName="Text"                Type="String" />             </InsertParameters></asp:SqlDataSource></asp:Content> 

View 2 Replies View Related

Passing A Date Name To A Database Back Up

Oct 10, 2000

I am trying to pass a datename in to a database backup. The date is to be retrieved from the 'monthname' column which is stored in the 'tblbill' table. I have included my ridiculous bit of code below (which funnily enough does not work!!) just so that you can see what I am trying to do. Can anyone help?

DECLARE @name varchar(10)
backup database Bill to Disk ='c:@name.dat' with init

@name =select distinct monthname from tblbill where monthname is not null

cheers,
Dennis

View 2 Replies View Related

Problem With Adding A Date Field To A Database When It Is Null

Jun 21, 2005

I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database:  '---------------------------------------------         ' name: Button_Click()         '---------------------------------------------        Sub Button_Click( s As Object, e As EventArgs )                        Dim strConnect As String              Dim objConnect As SQLConnection              Dim strInsert As String             Dim cmdInsert As SqlCommand            
             'Get connection string from Web.Config             strConnect = ConfigurationSettings.AppSettings("ConnectionString")
             objConnect = New SqlConnection(strConnect)                            strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )"             cmdInsert = New SqlCommand( strInsert, objConnect)             cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value )             cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text )             cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text )             cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text )
             objConnect.Open()             cmdINsert.ExecuteNonQuery()             objConnect.Close()                                  'Display the results "page"                  DisplayResults() 
        End Sub Here's the code for the form:         <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">         <div align="center">    <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">     <tr>      <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>      <td>&nbsp;</td>     </tr>              <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Client's Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <asp:dropdownlist id="dropClient" runat="server" />        </p>      </td>     </tr>    <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Domain Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />               </p>      </td>                         <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Registrar:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />       </p>      </td>     </tr>     <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Expiration Date:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />       </p>      </td>     </tr>               
     <TR>      <TD>      </TD>      <TD align="center">       <asp:Button                            Text="Submit"                            OnClick="Button_Click"                            Runat="Server" />      </TD>     </TR>    </table>  </form>  </div>

View 1 Replies View Related

Passing NULL Value Not Working

Oct 24, 2007

I currently have a stored procedure that looks something like this  SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@Name+'%', Title)I have a form that supplies this value.  This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not.  On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,SqlDataSource1.SelectParameters.Add("@Name", TextBox1.Text);It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why.  Thank you in advance /jcarver 

View 4 Replies View Related

SQLDataSource Parameters Passing Null

Aug 23, 2007

 I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!?  I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
 I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>

View 3 Replies View Related

Passing NULL-value Into Package Variables

Dec 20, 2006

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

View 3 Replies View Related

Passing NULL To DataSet Parmameter

Apr 13, 2007

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:



@ID = <Null> ---- ERROR - [BC30201] Expression expected

@ID= ---- ERROR - Return statement in function must return a value

@ID = Null -----ERROR - Null constant not supported use System.DBNull instead

@ID = System.DBNull ----ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@ID=System.DBNull.Value --- NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.



Does anyone know another magic value I can pass to get this to work?



I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.



Thanks in advance.



Eric Wild



PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!







ALTER PROC [dbo].[spGetLaborRole]
@ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @ID) OR (@ID IS NULL) )

View 3 Replies View Related

Textbox Not Passing Null To Stored Procedure

Aug 25, 2006

Hi,I have a stored procedure which expects a Null value if a user does not enter something into a TextBox. The query should return all rows if this case.The stored procedure works as expected in Query Analyzer, but my application does not seem to send a Null and no rows are returned. Any ideas?Thanks.ps I am using SqlDataSource and GridView, this is the first time I have tried to connect to a database with asp.net.

View 3 Replies View Related

Passing Null Values To Stored Procedures

Aug 29, 2006

Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!

View 4 Replies View Related

DNN DAL SqlDataProvider Passing NULL To SQL Stored Procedure

Jun 1, 2006

Hello,I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value.  My SQL Table boolean column allows nulls.I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table.  One of those fields is a boolean.  I create an instance of the InfoObject in my code and assigns values to the various properties.  The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything.  I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL.  If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens.  How do I pass a null to the SQL database for a boolean field?  I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean."  Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?Any help would be greatly appreciated.  I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.Thanks in advance for any help.

View 2 Replies View Related

Passing Null Value From Front End Access To SQL Back End

Dec 19, 2007

Hi,

I'm not sure if this question should be in this thread but anyway...

I have a form (Mricosoft Access front end) in which the user enter a Pending Date in one of the fields. The date is populated into my backend (SQL Server) withouht problems.
Whenever the issue which had the pending date in the field is resolved I'd like to delete this date from this field and also from the back end. At the moment I'm not being able to pass the "value" Null to my back end (probably because it's a datetime type). I've searched the net throughout and I couldn't find a way around it. Only found solution to when the data is not date but numbers/letters, etc... Any ideas on how can I sort this out?

Many thanks
Emanuel

View 3 Replies View Related

Passing Null To Stored Procedure In Reporting Services

Jun 27, 2007

Hello,



I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.



Thanks.

View 15 Replies View Related

Passing Null Value Parameter To A Data-driven Subscription

Jun 13, 2007

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.



When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.



I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.



Has anyone else experienced this issue and do you have a resolution?



Thanks in advance for your assistance.

View 5 Replies View Related

Passing Date Value

Oct 18, 2005

Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH

View 1 Replies View Related

Passing Date Range To A Subreport

Nov 2, 2007

I am passing a date range to a subreport which is a table that I want to show a list of months in a specific range. I am passing:

FromDateShipedYearMonth - ([Date Shipped].[Month].&[2006]&[4]&[12]
ToDateShippedYearMonth - ([Date Shipped].[Month].&[2007]&[4]&[11])

I have parameters set up in my Main report:


<Subreport Name="subreport2">

<Parameters>

<Parameter Name="FromDateShippedYearMonth">

<Value>= "(" &amp; Code.PreviousYear(Parameters!DateShippedMonth.Value) &amp; ")"</Value>

</Parameter>

<Parameter Name="ToDateShippedYearMonth">

<Value>="(" &amp; Parameters!DateShippedMonth.Value &amp; ")"</Value>

</Parameter>

</Parameters>

And in my Subreport:


<ReportParameter Name="FromDateShippedYearMonth">

<DataType>String</DataType>

<Prompt>FromDateShippedYearMonth</Prompt>

</ReportParameter>

<ReportParameter Name="ToDateShippedYearMonth">

<DataType>String</DataType>

<Prompt>Year - Month</Prompt>

</ReportParameter>

</ReportParameters>

I would like to display data from 12/2006 through 11/2007. Instead I get a list of 1/2006 through 12/2006.

Any ideas?



View 1 Replies View Related

Help Altering Database Table From NULL To NOT NULL

Jun 28, 2004

I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks

View 7 Replies View Related

Transact SQL :: Database Null Value And String Null

Oct 27, 2015

We have a case where in we should show date based on conditions for e.g if we had a column defined as

col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition

Normaly date values are stored here e.g under col 20150901 .

Case 
when col>'20150901' then 'NULL'
else col
end as Derivedcol

Note this is an extract process and we are presenting data by pumping the data in a table .

Now there is another similar column -colz varchar(10)  which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.

So whats the difference between database null and string null ?

 How can we show database null for the case condition instead of string "null"?

View 10 Replies View Related

T-SQL (SS2K8) :: Passing Table Name And Date Value As Variable

Jun 10, 2015

I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".

Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)

[Code] ....

View 9 Replies View Related

Passing Date Variable In British Format

Jan 1, 2014

I want to pass a couple of date variables in British date format to a function

Here's the function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 7 Replies View Related

Passing Date Parameter To Oracle Query

Aug 16, 2006

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is


WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?

View 11 Replies View Related

Stored Procedure - Passing Date Parameters Failed

Aug 4, 2015

I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.

The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.

The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.

Here is a portion of the stored procedure:

alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)

In Excel VBA, the code passing the parameters are:

cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)

where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.

The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.

View 5 Replies View Related

Passing Date Fields From Form To Access ADP Views

Jul 20, 2005

I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq

View 1 Replies View Related

SQL Server 2012 :: Passing Date Parameter To Stored Procedure?

Oct 9, 2014

I am trying to pass a date parameter to a stored procedure.

I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.

Basically, this works:

DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )

But this returns an error:

DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )

What am I doing wrong?

View 8 Replies View Related

Drill Through Report -- Passing Date Parameters -- Resulting In RsReportParameterTypeMismatch

Jul 4, 2007

All,



I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:



4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)

which displays correct data.



However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:



7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)



The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).



I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?



Your help is much appreciated.



Thanks.

View 5 Replies View Related

Default Date When Not Null?

Apr 23, 2008

I have a table that has a date field that can't be null.  When a new record is created, if a date is not passed in, I want the default to be the current date.  How can I set that?  I tried Now, Today, GetDate, etc.  Diane 

View 2 Replies View Related

Date IS NULL Not Working......?

Jul 20, 2004

Hi,

I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.

Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?

thank you for reading,
YakoBay

View 3 Replies View Related

How To Use BCP With Null Date Field

May 28, 2008

My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.

Is there a way to get BCP to accept empty date fields?

View 3 Replies View Related

MIN Date Without Displaying NULL Value

Sep 24, 2013

I have 3 dates one of them could be NULL . I do not want my min to display it as NULL.Below is my script. Is there a way I could do it?

select Order_id, customer_id, date_1, date_2, date_3
into #temp1
from ##Temp_dates (nolock)
Select Case when (date_1 < date_2 and date_1< date_3) then date_1
when (date_2< date_1 and date_2< date_3) then date_2

[code]...

View 4 Replies View Related

Doing A 'between' On A NULL In A Date Field

Mar 29, 2007

Hi everyone,

I have two tables - caseinfo and steps.

Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.

Steps shows the steps the case has gone through and the dates of those particular steps.

I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.

If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).

Does anybody have any ideas?

Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.

Thank you for reading.


--- create sample data
set dateformat ymd

declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)

insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'

declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)

insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'

--- the main idea (with the wrong results)

select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate

--- expected result

declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)

insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'

select *
from @expresult

View 1 Replies View Related

How To For Date Is Not Null Or Nothing Condition

Jan 11, 2007

How can i write a condition for like isdate(Fields!DueDate.value)

I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.

Thank you very much for the information.





*********************************************

=IIF(isdate(Fields!DueDate.Value), ................

********************************************

View 3 Replies View Related

Date Field Cannot Be Null!

May 30, 2006

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved