0

I have a value 

20 01 01 29 13 11 58 15 46 52 00 00 00

which got migrated to a SQL Server table of column type binary as 0x20010129131158154652000000. If we observe it is exact value just prefixed with 0x, which is fine.

Now, when I am trying to insert a value which should be current date and time with 6 precision millisecond it is converting to a hexadecimal value like 0x00000000000000A87200F31D11.

Please help me find a solution which should give the same time stamp value just by prefixing 0x even the SQL Server table column type is binary. That is if am passing the value as 2018-01-24 15:08:59.780, it should insert into SQL Server table of column type binary(13) as 0x20180124150859780

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
VRaju
  • 21
  • 1
  • 8
  • Can you explain why you would want this? – Aaron Bertrand Jan 24 '18 at 15:31
  • This works for `datetime` or `datetime2(>=3)`, but I still don't understand why you'd go to all this trouble instead of just storing it as the right type in the first place: `SELECT CONVERT(binary(13), '0x' + CONVERT(CHAR(8), @d, 112) + RIGHT(REPLACE(REPLACE(CONVERT(CHAR(12), @d, 14), ':', ''),'.','')+'0',10) , 1);` – Aaron Bertrand Jan 24 '18 at 15:40
  • Thank you very much Aaron Bertrand.This worked for me.The reason why I am having this requirement was, I am migrating data and changing database for a particular application from DB2 to SQL server. I have used "SS migration assistant" tool to complete migration from existing db. When I am using insert to scripts for adding new records I am facing issue with the binary data which is getting inserted to sql server.In simple terms there is a inbuilt funtion called Generate_Unique(id) in IBM db2 which will give unique timestamp value in binary format.I am trying to get a similar fx in sqlse also. – VRaju Jan 25 '18 at 04:32
  • Hi, In my sql server database as I told in my previous query the time stamp columns are defined as primary key. When I am trying to insert records through insert statement and below function I am not getting unique values. Can someone please suggest me how to achieve unique time stamp values in the SQL Server even when we are inserting hundreds of records as batch through below function? @AaronBertrand please suggest if have any idea about this? My function is in the comments below – VRaju Jan 29 '18 at 06:32
  • CREATE FUNCTION dbo.unique_id_gen() Returns Binary(13) as Begin Declare @d datetime2(6), @ret_variable binary(13); --select SYSDATETIME (); set @d = SYSDATETIME (); SET @ret_variable= (SELECT CONVERT(binary(13), '0x' + CONVERT(CHAR(8), @d, 112) + RIGHT(REPLACE(REPLACE(CONVERT(CHAR(16), @d, 14), ':', ''),'.',''),13) , 1)) return(@ret_variable) END I am trying to achieve unique values for below sql statment select dbo.unique_id_gen(),dbo.unique_id_gen(),dbo.unique_id_gen() – VRaju Jan 29 '18 at 06:32
  • So you want three "random" values based on the time, returned on the same row at a single point in time? Maybe you should stop looking at date/time as the source for your "random" values? – Aaron Bertrand Jan 29 '18 at 12:30
  • @AaronBertrand :The value shouldn't be random but next millisecond i meant to say if am getting 0x20180129140405743175000000,next function call should give0x20180129140417333176000000,following 0x20180129140427603173000000.but, should give as unique value as possible in terms of milliseconds. – VRaju Jan 29 '18 at 14:07
  • @AaronBertrand:I am even checking the possible option of adding a sequence after 3 precision milliseconds which would uniquely generate a number like this.I have tried with sequence number but couldn’t succeed since next values are not allowed inside functions. Is there any other better way to do this? 0x20180130150507252001000000 0x20180130150507252002000000 0x20180130150507252003000000 0x20180130150507253001000000 0x20180130150507253002000000 0x20180130150507253003000000 0x20180130150507253004000000 – VRaju Jan 30 '18 at 15:10

0 Answers0