Importing Null Date Fields
Nov 27, 2006
I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
View 4 Replies
ADVERTISEMENT
Apr 19, 1999
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
View 1 Replies
View Related
Sep 28, 1999
Hi:
I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.
-Nicole-
View 2 Replies
View Related
Jul 25, 2006
I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'.
I am assigning SqlTypes.SqlDateTime.Null.
But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database?
THANK YOU!!!
View 3 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Mar 10, 2008
I have designed an SSIS package and in a data flow task I've defined my source and destination components and mapped all the fields. The task works fine as long as I have the same number of fields in my CSV file as what I have defined in the task.
The issue is that if my CSV contains more fields than what are declared in the mapping, then the extra fields at the end of each line are concatenated into the last column defined in my map. For instance:
CSV SQL
Product -> Product
M0 -> M0
M1 -> M1
M2 -> M2
This works fine, but if my CSV file looks like this:
Product
M0
M1
M2
M3
M4
The values in fields M3 and M4 are concatenated with M2 and all 3 are imported into field M2 in my sql table. Any ideas?
View 5 Replies
View Related
Jul 23, 2007
Hi All,
I have a file with several columns in Comp-3.
I have downloaded the UnPack Decimal component and, as it needs byte stream (dt_bytes) as input, so I set up an appropriate Flat File Source columns.
But I get this error:
[Flat File Source [2201]] Error: Data conversion failed. The data conversion for column "DTCDC" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2201]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "DTCDC" (2250)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "DTCDC" (2250)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DTCDC is first of the columns packed. These are mostly date columns packed into 5 bytes - should be unpacked to normal SQL date.
I've tried different locale , as suggested in other threads, but it didn't help.
Can anybody help me with this issue how can I do it in (VB.NET script ,importing as String??).
Thanks in advance
Michal
View 4 Replies
View Related
May 12, 2004
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.
View 4 Replies
View Related
Jul 15, 2004
I have a text file that is delimited by nulls. Any idea on the best way to get this into a SQL Server table?
View 9 Replies
View Related
Dec 11, 2007
I am importing csv files into sql 2000 using odbcbcp and am getting the '' symbols when there are null values. Has anyone ever seen this? Or know how I can prevent this from happening? I am required to use odbcbcp to import this data.
View 5 Replies
View Related
Sep 12, 2001
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
View 2 Replies
View Related
Apr 29, 2007
I've tried to import a text file into a table using the Import/export wizard.
My problem starts with some columns that should have NULL values (i.e. zero-length string) but the wizard doesn't recognize that it's NULL.
How do I solve the problem?
Thanks,
Mich
View 3 Replies
View Related
May 16, 2007
Hi;We just migrated to SqlServer 2005. When I import tables from fromSqlServer database into another the identity fields get switched offfrom being identities.How can I prevent that from happening?Thanks in advance for any informationSteve-------------------------------------------------------------------Microsoft SQL Server Management Studio9.00.1399.00Microsoft Analysis Services Client Tools2005.090.1399.00Microsoft Data Access Components (MDAC)2000.085.1117.00(xpsp_sp2_rtm.040803-2158)Microsoft MSXML2.6 3.0 4.0 6.0Microsoft Internet Explorer7.0.5730.11Microsoft .NET Framework2.0.50727.42Operating System5.1.2600
View 3 Replies
View Related
Sep 16, 2015
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>
[code]....
View 4 Replies
View Related
Apr 28, 2006
Is there a way to write a select statement that will pull only fields that are not null?
View 2 Replies
View Related
Jun 6, 2001
how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies
View Related
Apr 9, 2000
I am working on Sql Server 7.0 with a group of Visual Basic programmers that have made the following request:
1.They want all decimals/integer fields in the database to default to 0(zero).
Is there an way to do this for all fields instead of on an individual basis?
2.They don't want a null returned on any character string field.
What is the best way to take care of these 2 requests? Thanks in advance.
Karen Suenram
View 1 Replies
View Related
Sep 29, 2005
Hi,
I have a query like this one
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
Thanks
View 1 Replies
View Related
Jun 5, 2008
Here is the problem that I am struggling with. The structure is:
States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID
The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...
The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?
I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...
But I am wondering if there is a way to do the select in one shot?
Any idea? I appreciate your help,
Thank you,
Iulian
Regards,
Iulian
View 3 Replies
View Related
Mar 16, 2008
How can I make empty cells show NULL on my table? Some cells show NULL others won't. Does this mean that they have contents?
The reason being is that, when I use the code
Select *
From Employees
Where JobDescription1 Like '%montly%'
Those with empty jobdescription1 show with the legitimate results.
Any help please?
Thanks!
View 2 Replies
View Related
Nov 20, 2007
Im trying to use MS SQL server mananagement studio to greate a view to recive information from my database.
The problem i have is that i include a tabel where a post have "Null" then that post will not be shown i my qustion/View.
How can i make soure that i get all the post even if they have "null" in a field where it do not mather(its not a Key)
Greatful for very fast, help. Need this to be solved tomorrow :-(
Mitmit
View 1 Replies
View Related
Apr 26, 2006
Requirements:
1) Cannot have results in which both m.homephone & d.homephone are both null, or both m.workphone & d.workphone are both null in the result set
2) We also do not want to see any combos where one homephone is null and the other has only the char 0....same for workphone...no null & 0 combinations.
e.g.
m.homephone d.homephone
null 0
0 null
The phone fields are varchar
I know this is hidiously wrong but is my first sloppy attempt at best:
select m.number, m.homephone as master_homephone, d.homephone as debtor_homephone, m.workphone as master_workphone, d.workphone as debtor_workphone
FROM master m
INNER JOIN debtors d ON d.Number = m.number
where (d.homephone <> m.homephone OR d.workphone <> m.workphone)
AND (d.homephone IS NOT NULL AND m.homephone IS NOT NULL)
AND (d.workphone IS NOT NULL AND m.workphone IS NOT NULL)
AND NOT ((d.homephone IS NULL AND m.homephone = '0') OR (d.homephone = '0' AND m.homephone IS NULL))
AND NOT ((d.workphone IS NULL AND m.workphone = '0') OR (d.workphone = '0' AND m.workphone IS NULL))
View 7 Replies
View Related
Mar 9, 2007
I can't figure this one out...
I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.
Example:
_ITEMNUMBER_
1-528
K214-5
184PR
45678
As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).
I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.
I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.
Any ideas?
View 7 Replies
View Related
Aug 20, 2007
I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!
View 1 Replies
View Related
Nov 17, 2006
Hi there,
I have a fairly generic question regarding database design.
I am working on my first database using SQL Express.
I am testing the database by inserting records into the pre defined tables.
Sometimes I forget to input text into a non null field. When
this happens it displays an error and then skips a few rows
which makes my row numbering messy. For example say I have
Row 1, Row 2 Row 3 all nicely formatted but on Row 4 I make a mistake
this row then disapears and their is gap between 3 and 5 where 4 was.
Although I am just testing, I was wondering what happens in a real
scenario if an inexperienced data input person makes a few mistakes it
makes the database numbering systems look bad. In this case
is it common to make most fields NULL to allow for admin errors??
View 8 Replies
View Related
Mar 19, 2008
I have a result set stored in a temporary table variable. The fields need to have heirarchy when they finaly get displayed to the user. I need to replace the repeating fields per group with NULL and leave out only one set of unique records per group.
I want to do something like this- make the fields in red NULL while leaving out the ones in green.
{ Col.Group I } { Col. Group II }
Col1 Col2 Col3 Col4 Col5
------- ------- ------- ------- -------
ValA ValA2 ValA3 ValA40 ValA50 {
ValA ValA2 ValA3 ValA40 ValA50 Row. Group A
ValA ValA2 ValA3 ValA41 ValA51 }
ValB ValB2 ValB3 ValB40 ValA50 { Row. Group B
ValB ValB2 ValB3 ValB41 ValA51 }
I was able to achieve this by using @@ROWCOUNT with in a while loop by performing one UPDATE at a time (SET ROWCOUNT = 1). But i have to run Count(*) each time I loop through the update query to figure out the remaining number of rows I have per group as I need to leave out one untouched. But the Count function is taking too much time and I'm having performace issues.
Any idea how I might be able to do this in my stored proc. efficiently?
Thanks,
Samtadsh
View 8 Replies
View Related
May 22, 2008
Hi all
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null.
Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
Thanks
View 4 Replies
View Related
Jul 11, 2006
Hey everyone,
This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.
Here is my current SELECT statement:
SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1
My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.
The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.
Can someone tell me what I am missing? Is there a function that I can use for this?
Thanks,
View 8 Replies
View Related
Aug 16, 2007
Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is
Username
password
hello
12345
12345
hello
hai-123
haihai
haihello
hello-123
Once i run this query i am getting it as
Username Password hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.
View 2 Replies
View Related
Nov 28, 2006
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -
Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try
the new version is much more elegant: -
Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If
Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" ....
There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try
This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?
View 4 Replies
View Related
Dec 30, 2006
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code:
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open();
The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field.
Appreciate if someone could tell me what i am doing wrong. Thanks
View 6 Replies
View Related
Jan 4, 2007
hi,
I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
Thank you.
View 7 Replies
View Related
Apr 30, 2007
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07
View 2 Replies
View Related
