none
update smalldatetime column

    Question

  • hi everyone,

    i have this list of dates and  i need to update the dates to a tables' smalldatetime column

    i tried updating in various ways such as:

    begin tran update dw_fact_emrg_visits set release_date = cast('2012-10-03 23:59:00' as smalldatetime) where visit_id = 0160601196758;

    begin tran update dw_fact_emrg_visits set release_date = convert(smalldatetime,'2012-10-03 23:59:00') where visit_id = 0160601196758;

    anyway, nothing works. i keep getting this messages about error converting from string to numeric etc'

    i tried inserting the dates (it's just a few) to a temp table and then update from that table but still recieved error.

    ther must be a correct way to update a smalldatetime column

    TIA

    Sunday, September 01, 2013 2:21 PM

Answers

  • anyway, nothing works. i keep getting this messages about error converting from string to numeric etc'

    I suspect the error is because visit_id is not numeric (e.g. varchar) but you are specifying a numeric literal.  This will require converting the existing column values to numeric and result in an error if any values are not numeric.

    You don't need CAST or CONVERT as long as you specify an ISO 8601 date literal.  For example:

    UPDATE dbo.dw_fact_emrg_visits 
    SET release_date = '2012-10-03T23:59:00'
    WHERE visit_id = '0160601196758';


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 01, 2013 2:48 PM
  • Thanks CELKO for replying

    this update i want to perform is on a datwarehose

    visit_id is a string and it's not necessarily numeric. that'show i get the raw data and i don't want to intervene. about the SMALLDATETIME property the design of the datawarhouse was done long time ago and i wasn't involve at the time


    the error looks like a problem in converting the visit_id to the interger value provided.

    Can you try the selecting the table instead of update ( just for a check)

    select * from dw_fact_emrg_visits where visit_id = 0160601196758;

    If this does throw error try this ,

    select * from dw_fact_emrg_visits where isnumeric(visit_id)=0;  --gives list of visit_id values which cannot be converted to numeric type.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, September 01, 2013 6:57 PM

All replies

  • anyway, nothing works. i keep getting this messages about error converting from string to numeric etc'

    I suspect the error is because visit_id is not numeric (e.g. varchar) but you are specifying a numeric literal.  This will require converting the existing column values to numeric and result in an error if any values are not numeric.

    You don't need CAST or CONVERT as long as you specify an ISO 8601 date literal.  For example:

    UPDATE dbo.dw_fact_emrg_visits 
    SET release_date = '2012-10-03T23:59:00'
    WHERE visit_id = '0160601196758';


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 01, 2013 2:48 PM
  • Maybe try something like

    Create Table #dw_fact_emrg_visits  (visit_id BigInt, release_date smalldatetime)
    
    Insert into #dw_fact_emrg_visits
    Values (160601196758, '2012-10-03 23:50:00')
    
    Select * from #dw_fact_emrg_visits
    
    update #dw_fact_emrg_visits set release_date = '2012-10-03 23:59:00'  where visit_id = 0160601196758;
    
    
    Select * from #dw_fact_emrg_visits
    
    Drop table #dw_fact_emrg_visits

    SQL Server does an excellent job of converting text strings to dates.

    Now, the thing I find intriguing is that the statement treats visit_id as an integer - no quotes - but at a length that requires a bigint.  But then again, you have a leading zero in the value, which makes me think it may be a text string.  In order for your query to work the visit_id has to be varchar/char etc and the visit_id has to be quoted _or_ it has to be a bigint, because int isn't big enough.  The error message is consistent with this.

    I just ran across this week in a system migration - I was storing the PK from the originating system in a Notes field, and linking back to it with a filter for IsNumeric(Notes) = 1.  Unfortunately, there were some policy numbers that were numeric but not int, so the join became ...on X.ClassID = Convert(BigInt, Y.Notes).

    Maybe I was a little more sensitized to your dilemma <g>

    Good luck, 

    Tim Mills-Groninger

    Sunday, September 01, 2013 3:08 PM
  • Here are my two Cents. This is called implicit conversion.

    You can see this thread too:

    Is CONVERT_IMPLICIT Better Than CONVERT?


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    • Edited by Saeid Hasani Friday, September 06, 2013 2:52 PM
    • Proposed as answer by Saeid Hasani Friday, September 06, 2013 2:52 PM
    Sunday, September 01, 2013 3:37 PM
  • >> I have this list of dates and I need to update the dates to a tables' SMALLDATETIME column <<

    Why don't you use DATE instead? SMALLDATETIME is a bizarre propriety leftover from 1970's Sybase SQL Server and UNIX. Why is the visit_id such a long integer? Is it really a tag number string?  This is why we require DDL as minimal Netiquette in SQL forums and why your rudeness hurts you and wastes our time guessing. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, September 01, 2013 5:00 PM
  • Thanks CELKO for replying

    this update i want to perform is on a datwarehose

    visit_id is a string and it's not necessarily numeric. that'show i get the raw data and i don't want to intervene. about the SMALLDATETIME property the design of the datawarhouse was done long time ago and i wasn't involve at the time


    Sunday, September 01, 2013 5:18 PM
  • Thanks CELKO for replying

    this update i want to perform is on a datwarehose

    visit_id is a string and it's not necessarily numeric. that'show i get the raw data and i don't want to intervene. about the SMALLDATETIME property the design of the datawarhouse was done long time ago and i wasn't involve at the time


    the error looks like a problem in converting the visit_id to the interger value provided.

    Can you try the selecting the table instead of update ( just for a check)

    select * from dw_fact_emrg_visits where visit_id = 0160601196758;

    If this does throw error try this ,

    select * from dw_fact_emrg_visits where isnumeric(visit_id)=0;  --gives list of visit_id values which cannot be converted to numeric type.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, September 01, 2013 6:57 PM
  • Take a look at DATETIME(n)  to the precision you need. This is how Microsoft is moving to ANSI/ISO Standards, so you need to upgrade anyway. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, September 01, 2013 7:03 PM