Default Value or Binding = (getdate())

Ok I have a script to generate a database, and newly added to the database is a date fild for a specfic table. I have the 'Default value or binding' set to (getdate()) how exactly would you add that to the script for then the table is initialy generated. Or is it soemthing I would need another script to do right after the table generation.

This is the script for the table in question:

CREATE TABLE [dbo].[cust_file] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Any help would be great,
Tim Meers
Wannabe developer.

You just add "DEFAULT GETDATE()" after the NULL or whatever...

CREATE TABLE [dbo].[cust_file] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL DEFAULT 'GETDATE()'
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm pretty sure that's the syntax, but if not, I'll post again in about 2 minutes with the right stuff.

Please mark this post as the answer if it suits your needs :)

Thanks,

|||

Wow, I was way off :)

CREATE TABLE [dbo].[cust_file] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.cust_file ADD CONSTRAINT
DF_Table_1_added_date DEFAULT 'GETDATE()' FOR added_date
GO

Peace,

|||

Do you mean create a table with a date column that gets populated automatically?

CREATE TABLE [dbo].[Junk]([id] [int]IDENTITY(1,1)NOT NULL,[description] [varchar](50)NULL,[added_date]AS (getdate()))

By the way, in SQL Studio, you can right click a table, select "Script Table as... Create to..." and it will generate the Create Table script for you.

|||

You don't have to create constraint for default value. You 1st post is correct with the exception of 'getdate()'. You don't have to enclose it in quotes

CREATE TABLE [dbo].[cust_file]

(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

SGWellens:

Do you mean create a table with a date column that gets populated automatically?

CREATE TABLE [dbo].[Junk](
[id] [int]IDENTITY(1,1)NOT NULL,
[description] [varchar](50)NULL,
[added_date]AS (getdate()))

By the way, in SQL Studio, you can right click a table, select "Script Table as... Create to..." and it will generate the Create Table script for you.

Don;t think that is what the poster wanted. added_date will always return the current date & time

|||

khtan:

You don't have to create constraint for default value. You 1st post is correct with the exception of 'getdate()'. You don't have to enclose it in quotes

CREATE TABLE [dbo].[cust_file]

(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_id] [int] NULL ,
[filename] [varchar] (255) NULL ,
[filedata] [image] NULL ,
[contenttype] [varchar] (255) NULL ,
[length] [int] NULL,
[added_date] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Exactly what I needed, thank you very much for your assistance.

Tim Meers
Wannabe Developer

|||

I knew I had it right the first time... but when I "double checked" by doing "Generate Create Script" ... SQL gave me that crazy constraint crap :(

Glad someone got it.