Update DateTime from Date and Time

All replies

• 

  

  1

  Sign in to vote

  First of all, it's bad form to start any kind of Update query with untested code.  Always, always, always, when working with SQL, determine the data that will be written and the records that will be affected, by using your clauses in a Select query, first.
  
  That said, you should try to finnese the following code (as I'm certain it will produce the same error, and in the case that it doesn't, it won't inadvertently obliterate the data you have):
  
  Select convert(varchar(10),ph1_enityDatePart,5),+' '+convert(varchar(8), convert(varchar,cast(ph1_entityTimePart as datetime),108)) as newDate from ph1_rateTable;
  
  So, start with the basics.  What does this return?
  
  Select cast(ph1_entityTimePart as datetime) from ph1_rateTable;
  
  If the return is desirable, work more of the function into the select bit by bit until you've found the error.

  ---

  Dave Berry

  Tuesday, November 10, 2009 7:52 PM

  Moderator
• 

  

  0

  Sign in to vote

  Glora -
  
  I've got to say upfront - * updating CRM using SQL is never supported * - if you can use another tool and update through the API, you'll be better for it.
  
  
  Having said that, can you provide an example of the data in your datepart and timepart fields?
  

  ---

  Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell

  • Proposed as answer by DavidBerryMVP, Moderator Tuesday, November 10, 2009 9:16 PM

  Tuesday, November 10, 2009 9:04 PM

  Moderator
• 

  

  0

  Sign in to vote

  Scott
  
  I need some way to get the complete date into the datetime attribute. Here is the example.
  
  DatePart: 2009-10-30 23:01:00.000
  TimePart: 17:01
  
  Is there any other way to update 5000 records by concatenating these two attribute data ?

  Tuesday, November 10, 2009 10:18 PM
• 

  

  0

  Sign in to vote

  Hi David,
  
  I did check the data format returned by
  
  Select convert(varchar(10),ph1_enityDatePart,5),+' '+convert(varchar(8), convert(varchar,cast(ph1_entityTimePart as datetime),108)) as newDate from ph1_rateTable;

  and the format is 30-10-09 17:01:00, which is what I need to update in the date attribute.
  But, since the CRM DateTime is in the UTC format: 2009-10-30T17:01:00+02:00, I am still trying to find out how to get that in SQL statement.

  Tuesday, November 10, 2009 10:37 PM
• 

  

  1

  Sign in to vote

  declare @date as nvarchar(50)
  declare @time as nvarchar(50)
  set @date = '2009-10-30 23:01:00.000'
  set @time = '17:01'
  
  select 
   @date datefield, 
   @time timefield, 
   cast(@Date as datetime) as Date, 
   cast(left(@time,2) as int) Hours, 
   cast(right(@time,2) as int) Minutes,
   dateadd(n,cast(right(@time,2) as int),dateAdd(hh,cast(left(@time,2) as int),cast(@Date as datetime))) as DatePlusTime

  
  
  I created the above to show a conversion from the text to dates/time.
  
  Once the value is a true datevalue, you can set the datefield in CRM to its value. - (depending on your location, you will likely need to adjust for UTC time. - if you store the value as 00:00 and you're in the +5 timezone like me, CRM will display the value as 7pm on the previous day.)
  
  SQL updates to CRM are not supported, and it's an easy way to mess up the database. - the *approved* way would be to write a small program to convert the values and update the dates via the SDK. - that would also free you from the whole question of TimeZone conversion since CRM would then be adjusting the date for you.
  
  hope that helps..
  
  
  
  
  

  ---

  Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell

  • Marked as answer by DavidJennawayMVP, Moderator Monday, December 7, 2009 12:07 PM

  Tuesday, November 10, 2009 10:55 PM

  Moderator
• 

  

  0

  Sign in to vote

  Thanks Scott, I will try this in a while.
  
  Meanwhile, I am trying to concatenate both using javascript and display on the form before the form loads to the user. Let me see if I am able to get that..that way I do not have to update the table directly.

  Tuesday, November 10, 2009 11:27 PM