CODEWITHSUNDEEP
sql-server
everisk
everisk

Reputation: 183

SQL Server Data Insert

I have this query (dumping from another server)

CREATE TABLE [Reservation_reserve_room] (
  [id] int NOT NULL ,
  [reservation_id] int NULL ,
  [room_id] int NULL ,
  [room_name] varchar(255) NULL ,
  [reserve_date] date NULL ,
  [reserve_time_start] time(7) NULL ,
  [reserve_time_end] time(7) NULL 
)

INSERT INTO [Reservation_reserve_room] 
VALUES (N'1', N'1', N'1', N'Monday Room Sermmit', N'2012-03-12', 0x30373A30303A30302E30303030303030, 0x30383A30303A30302E30303030303030);

But when execute it, it has this error

[Err] 42000 - [SQL Server]Implicit conversion from data type varbinary to time is not allowed. Use the CONVERT function to run this query.

How do i fix it?

Upvotes: 0

Views: 1658

Answers (4)

Pranav
Pranav

Reputation: 8871

use CONVERT

CONVERT(TIME, CONVERT(BIGINT, 0x30373A30303A30302E30303030303030))

OR Simply

CONVERT(TIME, 0x0000E0FF01E0FF01E0FF01E0FF01E0FF0100000000)


    INSERT INTO [Reservation_reserve_room] VALUES (N'1', N'1', N'1', N'Monday Room Sermmit', N'2012-03-12',  CONVERT(time, 0x30373A30303A30302E30303030303030)
,  CONVERT(time, 0x30373A30303A30302E30303030303030));

Read this Documentation on MSDN

Upvotes: 0

Shahin
Shahin

Reputation: 302

Use 

CONVERT(time,CONVERT(varchar,0x30373A30303A30302E30303030303030))

good luck

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

The value you are entering into the table in time fields are not valid.

Use Cast(Value as Time) to convert into Time

INSERT INTO [Reservation_reserve_room] VALUES (N'1', N'1', N'1', N'Monday Room Sermmit',
N'2012-03-12', cast(0x30373A30303A30302E30303030303030 as  Time),
CAST( 0x30383A30303A30302E30303030303030 as  Time));

Upvotes: 0

thedrs
thedrs

Reputation: 1464

A time format I know is accepted in SQL always is '12 March 2012'. This format is good for many DB types and doesn't confuse the date and the month numbers.

Upvotes: 0

Related Questions