Error after converting int to date data type

• SQL Guy 1

  SSCoach

  Points: 15974

  More actions

  July 6, 2010 at 12:30 pm

  #220847

  We have a table with a column that is integer data type and represents data in the following format:

  yyyymmdd

  Here is simplified case of the table with just a few records:

  if object_id('dates','u') is not null

  drop table dates

  go

  create table dates

  (date_int int,

  date_date date)

  go

  insert into dates (date_int)

  values

  (20100628),

  (20100629),

  (20100630),

  (20100701),

  (20100702)

  go

  select * from dates

  go

  Now I update second column in this table which is date data type:

  update dates

  set date_date =

  cast(

  cast((date_int % (date_int/10000))/100 as varchar(2)) + '/' + cast(date_int % (date_int/100) as varchar(2)) + '/' + cast(date_int/10000 as char(4))

  as date)

  (5 row(s) affected) and no error

  However, If I try to select * from dates, I get a message:

  An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'date_date'. The GetBytes function can only be used on columns of type Text, NText, or Image.

• Grey Cat

  Mr or Mrs. 500

  Points: 524

  More actions

  July 6, 2010 at 12:49 pm

  #1189284

  I did the exact steps you posted and got no error.

• WayneS

  SSC Guru

  Points: 95409

  More actions

  July 6, 2010 at 12:55 pm

  #1189288

  I ran the steps you posted, but did not receive an error. It looks like you might be utilizing the GetBytes function when it's generating your error, but you didn't include that in what you posted.

  Also...

  Since your int field has the date stored in YYYYMMDD format, this is a simpler conversion:

  update dates

  set date_date = CONVERT(char(8), date_int)

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• SQL Guy 1

  SSCoach

  Points: 15974

  More actions

  July 6, 2010 at 2:42 pm

  #1189342

  I found out what the problem is I have SSMS 2005 connected to server 2008.

  Thanks.