Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
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
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
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.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View Complete Forum Thread with Replies
Related Forum Messages:
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
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" thanks
View Replies !
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
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" thanks
View Replies !
How To Pass Null Value To The Database If A Form Field Is Empty?
How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example: Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection) CmdUpdate.CommandType = CommandType.StoredProcedure CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9) CmdUpdate.Parameters("@User_fax").Value = fax.Text() ... strConnection.open() CmdUpdate.ExecuteNonQuery strConnection.close() And, the stored procedure inside Sql server: USE market1 GO ALTER PROC Form2_NewUser @User_id bigint, @User_fax char(9),... AS SET NOCOUNT ON UPDATE Users SET User_fax = @User_fax, ... WHERE User_id = @User_id SET NOCOUNT OFF GO Thank you, Cesar
View Replies !
Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL
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!!!!
View Replies !
How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
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? Please advise. Thanks. Best. K7
View Replies !
VALUE IN FIELD NULL OR VOID STRING!?
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
View Replies !
How Do I Convert As/400 Time Field Being Seen As Dt_I8 To String
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
View Replies !
Convert Int Data Into A Multi-field String
Trying to get a multi field grouping to print. Client + Req Number + Status Client and status are nvarchar fields Req Number is an int. Keep getting # Error for this field on the report. on the report. It works in Access - should it be this difficult? Thanks! Terry
View Replies !
Date Field Cannot Be Null!
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 Replies !
Doing A 'between' On A NULL In A Date Field
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 Replies !
How To Insert Null Into Date Field?
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!
View Replies !
Urgent : Error When Convert Date Field With BCP.
I have a text file like this : K045|13/03/1997 When I upload this text file to SQL Server 6.5, with the following fields : Code Chraracter 4 Tdate Smalldatetime I use BCP to upload this text file to SQL tables, but I got a wrong date, like : Text file : K045|13/03/1997 SQL Tables data : K045 Nov 19 1935 7:00PM Can anyone on the net can help me ? Thank's
View Replies !
Check For Null In Repeater Date Field
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
View Replies !
Setting A Date/time Field To Null
Hello! 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. How can I accomplish this? Thank you! Dave
View Replies !
How To Check Null Or '' In Date Field In Expression And ...............
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 ''
View Replies !
SQL Date Field To String
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
View Replies !
What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?
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?
View Replies !
Problem With Adding A Date Field To A Database When It Is Null
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>
View Replies !
Convert Field From VarChar To Int With Speical Characters In Field
Hello, 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. UPDATE myTable SET field = CAST(field AS int)
View Replies !
In A String Field Identify Not Date Records?
Hi All, I have a table called Table1 with a field called DateString with a nvarchar type. The records are like this; DateString 1/11/2007 9/27/2006 2/22/2008 1/10/2007 11/13/2007 8/21/2007 5/23/2007 2/16/2007 12345678900 11/20/2006 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. Thanks, Zee Thanks a million.. Zee
View Replies !
Using Date Field In Search String To Bind To Repeater
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) con.open() dtrBday = cmd.ExecuteReader() rptBday.DataSource=dtrBday rptBday.DataBind() dtrBday.Close() 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.
View Replies !
Convert To Null Where Data Is Blank
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]
View Replies !
Informix Date Type Field To SQL Server Datetime Field Error
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part I tried a couple of things: Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed. Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed. What options do I have that will work?
View Replies !
Create Date Field From Substring Of Text Field
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View Replies !
Compare Date Field To Text Field
Hi, I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want. The beginning of the SQL query looks like this: FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦ It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM. I hope someone out there can help me with making this comparison. I appreciate your help. Bill
View Replies !
SUM If Date Field &&>=inputted Field?
I need help. I'm one step short of getting what I need. Here is what I have: SELECT FilteredIncident.accountidname, FilteredIncident.ticketnumber, FilteredIncident.createdon, FilteredIncident.modifiedon, FilteredIncident.new_enduserfirstname, FilteredIncident.new_enduserlastname, FilteredIncident.responsiblecontactidname, FilteredIncident.statuscodename, FilteredIncident.title, SUM(FilteredActivityPointer.actualdurationminutes) AS TotalTime FROM FilteredActivityPointer LEFT OUTER JOIN FilteredIncident ON FilteredIncident.incidentid = FilteredActivityPointer.regardingobjectid GROUP BY FilteredIncident.accountidname, FilteredIncident.ticketnumber, FilteredIncident.createdon, FilteredIncident.modifiedon, FilteredIncident.new_enduserfirstname, FilteredIncident.new_enduserlastname, FilteredIncident.responsiblecontactidname, FilteredIncident.statuscodename, FilteredIncident.title HAVING (FilteredIncident.accountidname = @accountid) AND ((FilteredActivityPointer.actualstart >= @billtime) OR (FilteredIncident.statuscodename <> N'Problem Solved')) This pulls everything that started after a certain date (@billtime) AND everything that is not closed. This is what I want; however, I need the SUM(FilteredActivityPointer.actualdurationminutes) AS TotalTime to only calculate the TotalTime after the @billtime regardless of the status. I can easily code in the date in the format 03/04/2008 but I haven't figured out how to do it successfully. I tried an IIF within the SUM and just got a syntax error (I'm assuming it is because FilteredActivityPointer.actualstart is a date/time.) Essentially, I need a report that shows all open tickets in the system AND all tickets that have been worked on since a specific date regardless of status that only totals the time spent during that date range. Many thanks to anyone who can help.
View Replies !
Default To NULL Instead Of Blank/empty String During Flat File Import
Hi, In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables. The table may contain column that are NULLable BUT there is NO DEFAULT for them. If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string? I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way: 1- set the DEFAULT(NULL) for EVERY column that needs this behaviour 2-set up some Derived Column option in the package to return NULL if the value is missing. Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns. Thanks Anatole
View Replies !
Varchar And Blank Spaces In SQL DB Field
I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric
View Replies !
Find Records With A Blank Field
I want to be able to use a query to display all the records in the 6.5 database that have no data in the STATUS field. This is the query I thought would work....."SELECT * from travel_date WHERE status="''" But, that is not working. Can someone please help me figure out the right way to wrtie this? I appreciate your help!
View Replies !
Text Field Comes Back Blank When Using ADO
Originally, I hit this problem with PHP. But then I was able to reproduce using VBScript. I have a table with a couple of text fields, and one Real field in a table. One row of data in the table. Both text fields set, but the real field doesn't even need to have a value set. Connecting to SQL 2005 using a DSN - ODBC. I'm using a stored procedure I named ExecIt that runs a SQL statement that is sent in and returns records. If I use cursor location adUseClient, it returns values for the text fields. If I use asUseServer, it returns blank values. If I use a SQL statement directly, it works. Only fails when using SQL statement by way of ExecIt. In each case, cursor type is adOpenStatic or adOpenDynamic. Using this combination fo cursor type and cursor location, it mimics what I see in SQL profiler when PHP is using ODBC. Anyone have a solution? Table: CREATE TABLE [dbo].[test3]( [ID] [int] IDENTITY(1,1) NOT NULL, [text1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [text2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [real1] [real] NULL, CONSTRAINT [PK_ReworkLog2] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Stored Procedure - ExecIt: create PROCEDURE [dbo].[ExecIt] -- Add the parameters for the stored procedure here @Statement nvarchar(max) AS BEGIN exec (@Statement) END DSN has been set up through ODBC manager. Can be using 2005 native driver, or older sql server driver. Code: dim txtOut set conn1=CreateObject("ADODB.Connection") conn1.Open "my-DSN","my-userid","my-password" sql="exec execit 'select * from test3 where ID=1'" set rs = CreateObject("ADODB.recordset") rs.CursorLocation = 2 'adUseServer - 2, adUseClient - 3 rs.CursorType = 3 'adOpenStatic rs.Open sql,conn1 if not rs.EOF then txtOut = "Results" & vbcrlf txtOut = txtOut &"text1:" & rs("text1") & vbcrlf txtOut = txtOut &"text2:" & rs("text2") & vbcrlf txtOut = txtOut & vbcrlf end if msgbox txtOut rs.Close conn1.Close
View Replies !
Blank HostName Field In Enterprise Manager
Hi all - I am stumped as to why this field is blank for some of my database connections. We have several Citrix Servers using ODBC to connect to our SQL Server 2000. One server's connections all display its hostname correctly, whereas the others display nothing. I've compared the Client Network Config screens from withing the ODBC sources, which are the same, and also compared the contents from the CLICONFG tabs. Does anyone know where this information is set on the client to display it to the SQL Server? Is it a registry entry somewhere on each client??? Thanks in advance, Simon.
View Replies !
Selecting Field From Second Table If Same Field In First Table Is Null
Hi, I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres. Another table (Employee) contains a default costcentre for each employee. Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period. My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre. This is my query so far: SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours FROM TransActualHistoricalMaster INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours This next bit is what I am unsure of. I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table. Any assistance would really be appreciated. Thanks.
View Replies !
Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table, I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch. I have no choice of making date column in SQL to string or Varchar etc, is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007). many many thanks,
View Replies !
Should SQL Concatenation Return A Null Value If One Field Is Null?
In an SQL statement which concatenates several fields I get a nullvalue returned if any one of the fields are null.Is this to be expected?For example :SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' +tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' +tblMember.memberCountry + '<br> ' + tblMember.memberInstitution ASconcatAddress FROM tblMember WHERE memberSurname='Cardy'returns a null value if eg tblMember.memberInstitution is null.Am I doing something wrong, if so I would be grateful for your help.Otherwise it would be useful to know if there is some kind of workaround which can be used within the SQL statement (which is beingused in a stored procedure),Best wishes, John Morgan
View Replies !
Can You Pass Field Names In Stored Procs?
CREATE PROCEDURE [dbo].[removeContact] @RemoveType VARCHAR (50), @ListID INT, @TargetField VARCHAR (50), @TargetValue VARCHAR (150), @RowsAffected INT OUTPUT AS UPDATE [tblContacts] SET @RemoveType = 'yes' WHERE [list_id]=@ListID AND @TargetField = @TargetValue SELECT @RowsAffected = @@ROWCOUNT GO declare @P1 int set @P1=0 exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com', @P1 output select @P1 I've tried this with and without the square brackets. Either way, nothing gets updated. Am I barking up the wrong tree?
View Replies !
Convert String To Date Independent Of Date Format
Hi, I Have this simple convertion in a Script component Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4)) this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format. but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server. How do I convert the string to date not depending on the SQL server language. thanks.
View Replies !
Detect And Convert An Empty String (from Textbox) To Null?
Hi all,I have this code that I use for my Search function:SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM t_musicWHERE (@MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @MUSIC_TITLE + '%') AND (@MUSIC_ARTIST IS NULL OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @MUSIC_ARTIST + '%')) But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.) I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet. Thank you all,Kenny.
View Replies !
|
TRACKER
