Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
Code:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
I am struggling getting a time field from as/400 into SQL 200 using SSIS. The time field for some reason is being seen as dt_I8, And every type of conversion I try to do with it, it gives me an error about truncation. Example of my time field 12.35.00
How do you convert time field from as/400 which sql is seeing as DT_I8 to a string so that I can combine it with a date field to put it into a database timestamp field. Stacy
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.
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'
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
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'
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.
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount, CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record: (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null. How could I do it?Please advice!
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
I'm about ready to pull my hair out. I have a repeater control, and a date field. If the field is a valid date, I'll use it to calculate and display the person's age. Otherwise, I want to display "N/A". My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date. It makes sense for these records to leave the date Null. This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label) Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If If inmate.DATE_OF_BIRTH Is DBNull.Value Then If IsDate(inmate.DATE_OF_BIRTH) Then LblAge.Text = "Age: N/A" Else LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH) End If End If End IfEnd Sub How can I resolve this?Diane
I am using a Ms-Access DS which is accessed by a website's server-side scripts.
What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.
I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns
The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'
I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'. In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''
Hi, What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
I am running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:
-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error. Any suggestions? I would prefer not to have to change every stored procedure we have which may do this. Thanks for any help. MaryKate
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
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> </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>
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
It may not have date records which are just string like in this example is 12345678900. I need a SQL script which can identify those records which are not of date type. Please help.
i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:
sub page_load(sender as object, e as eventargs) dtmDate=DateTime.Now.ToString("M") con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")
cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)
I know this isnt quite right. i get errors when it hits my repeater.
the error i am getting is :Syntax error converting string to smalldatetime data type. if someone could give me a push in the right direction here it would be greatly appreciated.
Hi, where I return the address details, in some of the fields they are blank, I need these blank fields as nulls, how do i do that ?
Thanks
Code Snippet SELECT a.[Account Name], a.[Line Address 1], a.[Line Address 2], a.[Line Address 3], a.[Line Address 4], b.* FROM ( SELECT abCUSA AS Company, abCUSB AS Account, 0 AS DeliverTo, abcnam AS [Account Name], abcad1 AS [Line Address 1], abcad2 AS [Line Address 2], abcad3 AS [Line Address 3], abcad4 AS [Line Address 4], CONVERT(VARCHAR(20), abCUSA) + CAST(abCUSB AS VARCHAR(20)) AS [Customer Number] FROM LIVEAS400.S65C422B.WRFDTA.PARNADR ) a Inner JOIN ( SELECT SLDATE as Date, SLCUSA as [Company Number], SLCUSB as [Customer Suffix], SLDELN as [Deliber To Number], SLTREF as [Trans Reference], SLCNAM as [Deliver To Name], CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) AS [Customer Number], CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) + CAST(SLDELN AS VARCHAR(20)) AS [Full Deliver To Number], -- SLTRT2 as [Trans type], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total, SLDESC as [Trans Description] FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR ) b ON a.[Customer Number] = b.[Customer Number]