Need help with Create table script and Insert record.

• pirzadaz ali

  Valued Member

  Points: 68

  More actions

  April 3, 2010 at 1:33 am

  #386636

  Hi,

  I got a small script from someone to use but need little help on How to make table and Insert record.

  CREATE TABLE [dbo].[Query](

  [BookID] [int] NOT NULL,

  [ASIN] [nvarchar](10) NOT NULL,

  [CategoryID] [int] NOT NULL,

  [Description] [ntext] NOT NULL,

  [Author] [nvarchar](256) NOT NULL,

  [PublishDate] [datetime2](3) NOT NULL,

  [AddedDate] [datetime2](3) NOT NULL,

  [MemberiD] [int] NOT NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

  INSERT [dbo].[Query] ([BookID], [ASIN], [CategoryID], [Description], [Author], [PublishDate], [AddedDate], [MemberiD]) VALUES (1, N'0596523092', 1, N'This comprehensive book teaches you how to build...', N'Some Name', CAST(0x0300000000D5300B00 AS DateTime2), CAST(0x0300000000F4300B00 AS DateTime2), 1)

  If you see in Table there is

  "datetime2" mentioned. Is it user data type? any idea how to make that and give it (3) length?

  In Insert script the cast will be

  CAST(0x0300000000D5300B00 AS DateTime2). Giving conversion error as well.

  Why we need to use varbinary at the first place and why not use DateTime datatype?

  Thanks

• vaibhav.tiwari

  SSCommitted

  Points: 1976

  More actions

  April 3, 2010 at 2:35 am

  #1145256

  DateTime2 is not the datatype in sql 2005. it is the datatype of sql server 2008.

  See BOL for more help on new datatypes for dates.

  Vaibhav K Tiwari
  To walk fast walk alone
  To walk far walk together

• pirzadaz ali

  Valued Member

  Points: 68

  More actions

  April 3, 2010 at 3:16 am

  #1145264

  Thanks fo the reply.

  I tried it in SQL2008 but still get an error like

  SELECT CAST(0x002D248F00000000 AS datetime2) and ERROR is "Type datetime2 is not a defined system type"

  and below also not working in SQL2008 example from BOL

  SELECT

  CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'

  ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'

  ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS

  'smalldatetime'

  ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'

  ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS

  'datetime2'

  ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS

  'datetimeoffset';

• suril.developer

  Newbie

  Points: 7

  More actions

  April 3, 2010 at 4:01 am

  #1145273

  http://msdn.microsoft.com/en-us/library/ms180878.aspx

• Paul White

  SSC Guru

  Points: 150467

  More actions

  April 3, 2010 at 4:16 am

  #1145275

  pirzadaz ali (4/3/2010)

  ---

  ERROR is "Type datetime2 is not a defined system type"

  This means you are not connected to a SQL Server 2008 server.

  The important thing is the version of the server you connect to - not, for example, that you are using 2008 Management Studio 🙂

  You can determine the version of SQL Server you are connected to using:

  SELECT @@VERSION;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi