Populating A SQL Table With SmallDateTime Values
Mar 24, 2008
I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ...
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
---Jim
View 3 Replies
ADVERTISEMENT
Feb 24, 2004
Hi,
I am having a column subdate as smalldatetime.
The field can have null values. But I am not able to insert null values into the database.
i tried using subdate="". But it inserts some min value of my calendar popup.
Is there a way that i could insert null values into smalldatetime field of the SQl database.
Thanks,
-sriram
View 9 Replies
View Related
Apr 25, 2008
Hello, all.
I am new to SQL querying and I came across an issue while experimenting.
Say, I have two tables like the following, a Users table at the top, and a Roles table at the bottom (drawing the issue as I'm not fluent in English to explain):
(Users table)
UserId | RoleId | RoleOldId
-------+--------+----------
1......|x.......|11........
2......|y.......|22........
3......|z.......|22........
(Roles table)
RoleId | RoleOldId | RoleName
-------+-----------+-------------
10.....|11.........|Anonymous....
20.....|22.........|Superuser....
30.....|33.........|Administrator
The Roles table was changed to include a new column Roles.RoleId and the old column is named Roles.RoleOldId. Same is done for Users table.
Assume these are not foreign and/or primary keys. How can I fill Users.RoleId with new Roles.RoleIds, matching RoleOldIds at each tables? The resulting (x, y, z) set of Users table will be (10, 20, 20).
Thank you for your time.
View 3 Replies
View Related
Feb 14, 2004
hi
how can we insert null into a small datetime field (from client application VB)
View 2 Replies
View Related
Mar 25, 2008
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.
I have tried to find the values which are out of the smalldatetime range, with the following query
SELECT *
FROM midmar
WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'
Which doesn't quite work, and gives me values that are actually between those two values listed.
I have also tried having the WHERE clause read:
WHERE bday <'06/06/2079' AND
bday>'01/01/1900'
and that doesn't really work either.
What's going on? Is there likely another problem besides the structuring of my queries?
View 9 Replies
View Related
Jun 15, 2006
From a text file I'm importing date values by a variable.
dFecha = Left(Row.Column21, 4) & "/" & Mid(Row.Column21, 5, 2) & "/" & Right(Row.Column21, 2)
After that I've got values as "0000-00-00" and obviously sql (at target smalldatetime) doesn't support them. How do I for to solve?
If IsDate(dFecha) Then
.Parameters("@FecEnajenacion").Value = dFecha
Else
.Parameters("@FecEnajenacion").Value = vbNull
End If
TIA
View 1 Replies
View Related
Sep 9, 2015
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Note: Both col1 columns are of type nvarchar
View 6 Replies
View Related
May 19, 2004
I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.
Thanks
View 11 Replies
View Related
May 11, 2007
Hi, I have two tables:
1. RubricReportDetail with columns LocalPerf, Age
2. SppIndicator with columns Pct, Age
How can I populate the values of LocalPerf with Pct by matching
RubricReportDetail.Age = SppIndicator.Age ??
Please help me. Thanks in advance.
View 15 Replies
View Related
Apr 17, 2007
I am running a program that populates tables on my local database by querying another database.
View 2 Replies
View Related
May 14, 2008
Hi all,
I have a table that looks something like this: -
county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby
I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -
County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES
I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'
SELECT @sql
exec sp_executesql @sql
SELECT * from ey_report_temp
--------------------------------------------
DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)
DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0
begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'
exec sp_executesql @sql
end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp
drop table ey_report_temp
This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation
(1 row(s) affected)
(0 row(s) affected)
update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
(0 row(s) affected)
View 7 Replies
View Related
Jul 9, 2006
I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..
Table City:
CityID CountyID CountyName
1 3 NULL
2 2 NULL
3 1 NULL
Table County:
CountyID CountyName
1 Los Angeles
2 Contra Costs
I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.
Barkingdog
View 1 Replies
View Related
Feb 7, 2008
Hello,
Just when I thought I was starting to understand SSRS just a little and then I encounter a strange (maybe not too strange) of an issue.
I have a DataSet which runs a stored procedure and it requires 10 parameters. When I run it in the dataset portion itself it runs fine however, it will not work when I choose to Preview it. I refreshed my dataset and even rebooted my machine. Can anybody shed some light on this?
Thanks
View 6 Replies
View Related
Dec 30, 2007
While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"
and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension
Please help hwo to do both the things.
JJ
View 5 Replies
View Related
Jul 12, 2004
Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?
OR is there a way to have the sql reults paird up with a autonumbered ID?
Doc
View 2 Replies
View Related
Oct 6, 2006
Hi,
I have a flat file with columns from a geographical hierarchy such as:
Country Zone State County City Store Sub Store , etc.
The file also has data columns for months to the right of the above columns such as:
Jul Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:
Country Zone State County City Store Substore
USA Southeast FL Hillsborough Tampa walmart Fletcher
May04_ProdA ....Jun06_prodA May04_ProdB...Jun06ProdB
144 160 180 158
I need to upload this data into a staging table in SQL Server 2005 using SSIS, I created a table with the geographical hierarchy columns but am trying to figure out a way to load the monthly data. I can create 50 columns for the 50 months ( 25 months for each product) but that would be very crude.
Is there a better way of inserting data from this flat file into a destination table? I need all the data in the staging table in one upload.
Thanks.
View 11 Replies
View Related
Aug 1, 2006
I am having a torrid time trying to populate a FACT table in ORACLE.
I have so far tried two approaches -
1. Identified the change type for records - meaning I mark the record as inserts or updates based on the availability of the key in the FACT table. I used a conditional split and then used two OLE DB destination tasks to load the FACT table. But this failed as I was pretty much inserting on both the conditions (inserts and updates). So this attempt would count as a batch update process attempt.
2. Tried to also write a stored procedure in ORACLE destination database and then use that stored procedure to execute record by record using OLE DB Transformation task. But this did not fly as well due to the fact that SSIS could not understand the oracle SP and could not parse the information out.
Thank you
View 1 Replies
View Related
Oct 31, 2006
In my SSIS package I used the Web Service task to populate a SSIS variable with data from an XML feed. Then in the Data Flow I used a XML Source to import that data into a table on my SQL Server. When I looked at the data i noticed that the some of the fields contained CRLF pair. These CRLF pairs are being only populated by xml nodes that contain no data and are self-terminated. Below is a sample of the XML feed that is causing the problems. the DR_M_Initial and DR_Type1 nodes are populating CRLF's to the table.
Can anybody give me some insight on this problem. I am performing a workaround using the SQL REPLACE function in an Update statement to strip out ch(10) and ch(13), but this just seems a little cluegy to me.
Sample XML
---------------------------------------------------------
<?xml
version="1.0" encoding="utf-16" ?>
- <ROOT xmlns="">
<ROWRESULT>
<DR_NUMBER>1</DR_NUMBER>
<DR_L_NAME>SMITH</DR_L_NAME>
<DR_F_NAME>JOE</DR_F_NAME>
<DR_M_INITIAL />
<DR_TYPE1 />
</ROWRESULT>
</ROOT>
Thanks,
Tim Frakes
View 1 Replies
View Related
Dec 5, 2005
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
View 2 Replies
View Related
Jan 15, 2015
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
View 5 Replies
View Related
Aug 29, 2013
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies
View Related
Jul 13, 2007
Inside my SQL 2005 database I have a SmallDateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.
So, I'm looking for a way to drop the time off completely (for the query purpose) the database date when running a select statement. I don't want to touch SQL table data by modifying it, I just want to be able to query sql table using calendar control. Does anyone now how to accomplish this? Thank you in advance. - Nietzky
View 5 Replies
View Related
Apr 10, 2008
I'm experienced using queries to extract data, but I'm new to actually creating tables, except through Access. I work with many records of data, so populating the data by hand is not an option. I created a test database (ValTest) and a test table within that database (ClaimTest). I created a text file (DataTest.txt) with the same layout as I defined for ClaimTest. I want to populate ClaimTest with the data from DataTest.txt. I was told the only way to do that using SQL Server Express was to use a utility called BCP. So I found a page on the Microsoft website talking about BCP. It gave some examples, so I opended up the Command Line window, pointed to the directory that contains the database and text file and typed "BCP ValTest.ClaimTest in DataTest.txt -T -c". I get 3 errors: Named Pipes Provider: Could not open a connection to SQL Server [2], Login timeout expired, and An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
I tried looking around and finding where I could change the settings, in case that was indeed the problem, but was unable to find anything. Does anyone have any suggestions?
Thanks
View 10 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Jun 10, 2014
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies
View Related
Sep 11, 2015
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies
View Related
Jul 20, 2005
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies
View Related
Jul 26, 2014
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
View 2 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Mar 30, 2007
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
View 1 Replies
View Related
Apr 13, 2005
I need to populate a queue table with an orderid, and the rest of the order in an xml auto element format going into a text field.
The data is coming from another sql server table. I was going to do this is an sp and started with below
declare @request text
set @request = (select orderid,* from neworder
For XML auto,ELEMENTS)
However it errors on the statement. I can run the select by itself fine, but need to store the output.
Any ideas
View 1 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
