Getting 0 rows returned on query

so let me start will the basic table layout for all tables involved:


+----+----------+-----------+ | id | zip_code | time_zone | +----+----------+-----------+ | 1 | 00544 | -1 | | 2 | 00601 | -3 | | 3 | 00602 | 0 | | 4 | 00603 | -3 | | 5 | 00604 | 0 | +----+----------+-----------+


+------+---------------+--------------------+ | id | location_code | service_center_zip | +------+---------------+--------------------+ | 7119 | TX725 | 79714 | | 7121 | TX734 | 75409 | | 7122 | TX737 | 78019 | | 7124 | TX742 | 75241 | | 7126 | TX751 | 77494 | +------+---------------+--------------------+


+----+-----------------+------------------+--------------+--------------+ | id | truck_stop_code | create_date | gps_verified | central_time | +----+-----------------+------------------+--------------+--------------+ | 1 | CA428 | 05/01/2015 14:52 | 0 | NULL | | 2 | CA343 | 05/01/2015 19:10 | 0 | NULL | | 3 | CA223 | 05/01/2015 09:28 | 0 | NULL | | 4 | CA721 | 05/01/2015 07:55 | 0 | NULL | | 5 | MN336 | 05/01/2015 06:46 | 0 | NULL | +----+-----------------+------------------+--------------+--------------+

When I was working on this project an issue was noticed with the create_date column in transaction_record. It needs to be converted to central time, so I wrote an update query, but I have been unable to successfully set the central_time column. My query is below:


UPDATE t SET t.central_time = DATEADD(hour, z.time_zone,CONVERT(DATETIME, t.create_date, 120)) FROM eagle_devel.dbo.zip_code_time_zone z INNER JOIN eagle_devel.dbo.pricing_record p ON z.zip_code = p.service_center_zip INNER JOIN eagle_devel.dbo.transaction_record t ON t.truck_stop_code = p.location_code

This is what i get when I run the query

(0 row(s) affected)


The time_zone column in #zip_code_time_zone is not the standard UTC it is the difference to calculate to central

I am still working on this as we speak, just looking for some extra assistance to see if someone else can fix it faster than myself.


Try like this instead with little changes, table you are updating should be in FROM clause and then adjust the JOIN accordingly

SET t.central_time = DATEADD(hour, z.time_zone,CONVERT(DATETIME, t.create_date, 120))
FROM eagle_devel.dbo.transaction_record t
INNER JOIN eagle_devel.dbo.pricing_record p ON t.truck_stop_code = p.location_code
INNER JOIN eagle_devel.dbo.zip_code_time_zone z ON z.zip_code = p.service_center_zip

Category:sql Time:2018-11-06 Views:0
Tags: sql sql server

Related post

  • Getting row number for query 2012-02-05

    I have a query which will return one row. Is there any way I can find the row index of the row I'm querying when the table is sorted? I've tried rowid but got #582 when I was expecting row #7. Eg: CategoryID Name I9GDS720K4 CatA LPQTOR25XR CatB EOQ21

  • ADO cannot get rows from access query 2014-04-01

    Hi I am developing an application in Delphi which needs to get data from an Access database. My problem is that I can open tables, by query, table or dataset methods, and I can get rows from queries, but only when the row set is < 1200 records. Fo

  • How do I execute a stored procedure once for each row returned by query? 2009-05-20

    I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id How would I write query for th

  • does the number of row returns in query with order by affects performance in oracle? 2012-03-04

    I have a simple query like this: select * from ( select as y0_, this_.tempurature as y1_, this_.pressure as y2_ from mytable this_ where this_.start_time_local>=to_date('02/03/2012', 'MM/DD/YYYY') and this_.activity_type_pk<>12 and

  • Enforce only single row returned from DataReader 2011-10-20

    I seem to write this quite a lot in my code: using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { result = new User((int)reader["UserId"], reader["UserName"].ToString()); } if (reader.Read()) { throw new DataException("multiple rows return

  • Display message if nothing returned from Query 2012-03-20

    How do I display a message if nothing is returned from my query? I tried this: while($info2 = mysql_fetch_assoc( $data2 )) { // la la la lots of code here } else { echo "Nothing Returned"; } Only to get this error: Parse error: syntax error, unexpect

  • How do you get the row returned by a SQL query that includes PREPARE and EXECUTE in PHP? 2012-04-11

    How do you get the row returned by this SQL query using PHP: SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM mytable))); SET @sql := CONCAT('SELECT * FROM mytable LIMIT ', @r, ', 1'); PREPARE stmt1 FROM @sql; EXECUTE stmt1; Setting $query equa

  • How can I find out how many rows a MySQL query returns in Java? 2009-01-19

    How can I find out how many rows a MySQL query returns in Java? --------------Solutions------------- From the jdbc faq: .18. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set? No, b

  • How to get only second,third,fourth or fifth row of a query result?(SQL SERVER 2000) 2009-02-11

    I would need a suggestion or even a solution, how to get only the second,third... row of a query result with high performance. I know there is the possibility of row_number(SQL Server 2005 or higher) but not for SQL Server 2000 :-( My first try to ge

  • MySQL query works in phpmyadmin but no rows returned in PHP result set 2010-01-27

    I have the following MySQL query: SELECT SQL_CALC_FOUND_ROWS p.* FROM product AS p LEFT JOIN productCategory AS c ON FIND_IN_SET(, REPLACE(TRIM(p.categories), ' ',',')) WHERE ( IS NULL OR c.status = 'D' OR p.categories IS NULL OR TRIM(p.cate

  • Why is Wordpress $wpdb->query() response 1 when the rows returned is 0 2010-06-07

    I'm doing the following in a custom function: $exists = $wpdb->query($wpdb->prepare(' SELECT COUNT(*) FROM wp_%d_gdsr_data_article WHERE post_id = %d ', $blog_id, $post_id)); $exists evaluates to 1 even if no rows are returned by the query. Als

  • Any way to get the amount of rows returned using mysqli prepared statements? 2011-01-05

    Wondering if there is a way to get the amount of rows returned from a query using mysqli prepared statements, sort of like mysql_num_rows($query); --------------Solutions------------- Presuming you're doing something like: $result = $statement->ex

  • Codeigniter get number of row from search query 2011-11-01

    I have a query in my controller which searches for the term/keyword that is in the url. Example /search/keyword Then through my controller I perform the search doing: $viewdata['search_results'] = $this->Search_model->search(strtolower($this-

  • Get Number of Rows returned by ResultSet in Java 2011-11-28

    I have used a ResultSet that returns certain number of rows. My code is something like this: ResultSet res = getData(); if(! { System.out.println("No Data Found"); } while( { // code to display the data in the table. } Is there

  • How do I count the total number of rows returned from a WQL query? 2011-11-30

    I am trying to find the total number of CPUs on a computer via get-wmiobject. The query: select * from win32_processor returns more than 1 row if there is more than 1 processor on the computer. WQL doesn't have the count keyword like in TSQL. So I am

  • mySQL database query LIMIT help- How to create pages if number of rows returned exceeds 'x'? 2012-04-09

    I'm attempting to write a script that returns rows from a mySQL database. Basically, the users on my site receive "experience points" and go up "ranks" based on the number of experience points they get. I have a page called "recentexp.php" where all

  • Sql query to return SUM and # of rows that match query 2009-05-21

    Table UserData (UserID, Sales, Credits) I need to return the SUM of sales, sum of credits and the # of rows returned for a given date range. Is it possible in 1 query? --------------Solutions------------- SELECT COUNT(*), SUM(Sales), SUM(Credits) FRO

  • get row no through mysql query 2009-09-14

    hello sir How to get the row no through mysql query for each row? (i want to display sno) Please help me sir --------------Solutions------------- I believe you are looking to see if you can get the row number for each row through MySQL query right? h

  • select top 5 rows returned by particular query sql server 2010-07-08

    I want to retrieve top 5 rows returned by this query. How to do this select COUNT(trippackageID), trippackageid from tbl_holiday_booking group by trippackageID --------------Solutions------------- You are not specifying the order, do you want the pac

Copyright (C), All Rights Reserved.

processed in 2.444 (s). 14 q(s)