Delete and import data in a table in replication environment

Dear All,
I have made a replication between Server A and B. It work well
One date, the customer need to renew all data in one table. So I run a
script to delete the data then try to import such data from another outside
server. I used Sql server enterprise tool to import data,I make it
successful. But after runed the replication. I found the data have been
deleted in the table in Server B. The customer complain me that they can not
retrieve data from Server B. It make me sad.
So I just want to ask whethe the Sql server enterprise tool import
function can not triger to replication?
Regards
Jackson Chan
You don't mention the replication type, but my guess is that it is merge. If
so, if you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents. In
this case, you need to run sp_addtabletocontents to include the rows then
resynchronise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

delete 30 days from todays date?

I have a column that is to be updated to todays date - 30 days if it is older than that.

I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?

update DATAFILE
set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30update DATAFILE
set [Effective Date] = [Effective Date] - 30
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30|||Ok, it was so simple?

I found this code worked also.

update DATAFILE
set [Effective Date] = DATEADD(dd, -30, GetDate())
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

The date is supposed to be updated to todays date minus 30 days.
Its ok now. Thanks...|||Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.

Use the DATEADD and DATEDIFF functions instead.

blindman

Defining week ending date.

Hello,
I have a w ending date question. Here is my table (just for demo
purposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
wending a Saturday. Now, my client wants to change the w ending to
Thursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w number if you want to calculate acco
rding to the ISO
standard (where this w is w 38). SQL Server DATEPART considers this we
ek to be w number 39.
If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w. I think ISOWEEK respects thi
s setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w ending date question. Here is my table (just for demo purpo
ses)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the wen
ding a Saturday. Now,
> my client wants to change the w ending to Thursday (or whatever). I ha
ve no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the ws yourself, but
it gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w.
SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:

> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>

Defining Variables in Date fields within a Trigger?

Hi All,

I am creating an Insert Trigger with following example of code for you to go off(just an example)

DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

When checking the Syntax for the trigger it errors saying that - 'Error Syntax near '@.CREATIONDATE'

It works fine if I just insert a static value such as
{d '2002-10-10'}. Am I able to replace the static value with a variable and if so what will my syntax be? How would it look?

Thanks
Anthonyhow about:

Insert into fintest.dbo.glf_chart_acct
(chart_name, accnbri, descr1, date)
select 'Name', 'Code', 'Description', Creation_Date
from Inserted

SQL Server will automatically convert a string to a date and you have the advantage of handeling one or more records at a time!|||DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

How about instead of the {d @.CREATIONDATE} you either put just @.CREATIONDATE or try a CAST(@.CREATIONDATE as datetime)

Defining a "partial" foreign Key..how could I ?

I have a table A, which has 3 fields:

- code, subcode and description (code and subcode are PK)

and a table B which has 2 fields:

- date, code, value (date and code are PK)

So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?

Well this is kind of design issue...

You can't do it directly.. Your table design is wrong.

Try to use the following design:

CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key

SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key

|||

Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.

In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row

You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.

define default for date report parameter / analysis services

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.

So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.

SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]

I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.

|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.
|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||

this is a helpful tip!

|||

i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and then in alphabetical order when I sort like this

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

how do I get it to sort by real date?

Defaulting Date Parameter

Hello,

I have a report parameter StartDate. Properties are

DataTypeataTime

Prompt: StartDate

Default Values:

Non Queried : =NOW()

I set the default value to Now(). When I go to preview, the StartDate parameter is blank and its been locked & grayed out. I also tried

Today() and Globals!SystemTime but that does not work either. Is there any other solution to make this work?

Thanks

Raj

I have not seen any responses on this. Wanted to see if there are any ideas/ thoughts on this.|||

Try:

Code Snippet

=cDate(FormatDateTime(Now, DateFormat.ShortDate))

Larry|||HI,
You can give the following expression for the default value of startdate;
DateValue(now()).

Cheers,
Shri|||

Is this the only parameter in your report? If it is, then you shouldn't be having any problems.

If not, try entering the values for all the parameters that come before StartDate. Then you should be able to see the default value & it should not be greyed out any more.

-Aayush

Default values for StartDate and EndDate

Hi,
I have Start date and End date as parameters. Now is there a way to have the
start date always default to the start date of the current month...i.e
1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
month & current year?
How can this be done?
Thanks
--
pmudHi,
I have solved half the question reading from other posts. For the start date
I used =DateTime.Now.Addmonths(-1) . So it went back to the previous month.
But how do i set the day to 30 ?
--
pmud
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Enter this for your (Non-Queried) parameter defaults:
StartDate... (this is in the format "m/d/yyyy" which could be changed)
=CDate(Month(Now()).ToString & "/1/" &
Year(Now()).ToString).ToShortDateString
EndDate...(This takes the first day of the next month and subtracts one day.)
=DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
))).ToShortDateString
Hoep this helps. If anyone has a better solution I'd be happy to see it.
Fred
"pmud" wrote:
> Hi,
> I have Start date and End date as parameters. Now is there a way to have the
> start date always default to the start date of the current month...i.e
> 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> month & current year?
> How can this be done?
> Thanks
> --
> pmud|||Hi,
I put the logic into a Stored procedure and created a new dataset in the
report designer that references this SP. Then just reference the dataset in
the report paramaters section.
It suited what I needed here as multiple reports have the same default start
and end dates so rather than creating logic in each report the single SP will
do for all the reports. But that may not suit others.
"FredP" wrote:
> Enter this for your (Non-Queried) parameter defaults:
> StartDate... (this is in the format "m/d/yyyy" which could be changed)
> =CDate(Month(Now()).ToString & "/1/" &
> Year(Now()).ToString).ToShortDateString
>
> EndDate...(This takes the first day of the next month and subtracts one day.)
> =DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Month, 1,
> CDate(Month(Now()).ToString & "/1/" & Year(Now()).ToString)
> ))).ToShortDateString
> Hoep this helps. If anyone has a better solution I'd be happy to see it.
> Fred
>
> "pmud" wrote:
> > Hi,
> >
> > I have Start date and End date as parameters. Now is there a way to have the
> > start date always default to the start date of the current month...i.e
> > 1/CurrentMonth/CurrentYear and EndDate default to 31 or 30th of the current
> > month & current year?
> >
> > How can this be done?
> >
> > Thanks
> > --
> > pmud

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

default values

Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
Thx.Yes you can and it's dead easy.
In the Report Parameters dialog, in the Default Values section at the borrom of the form, select Non-queried and use the following expression:
=Globals!ExecutionTime
That'll insert the exact date and time of when the report is run as a default value if one isn't entered.
DF
"AG, NLB d.d." wrote:
> Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
> Thx.|||is it possible to make yesterday as default value?
>--Original Message--
>Yes you can and it's dead easy.
>In the Report Parameters dialog, in the Default Values
section at the borrom of the form, select Non-queried and
use the following expression:
>=Globals!ExecutionTime
>That'll insert the exact date and time of when the report
is run as a default value if one isn't entered.
>DF
>"AG, NLB d.d." wrote:
>> Is it possible to set report parameter of type datetime
on current date in case when user don't specify value?
>> Thx.
>.
>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
Jeff
Instead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.ph
x.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

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.

Default value of stored procedure parameter

Hi,

This works:

CREATE PROCEDURE MyProc

@.Date smalldatetime = '2005-01-01'

AS

...

But this does not

CREATE PROCEDURE MyProc

@.Date smalldatetime = GETDATE()

AS

...

I'm talking about sql2005. Can anyone help how to overcome this?

You'd have to set it inside the proc not at the definition level. Leave the default as NULL. Inside the proc check if the @.Date IS NULL, then assign the Getdate() to it. Let me try to phrase it "You cannot assign non-deterministic value to a parameter in proc definition".

|||

Not exactly the same, but usually works:

CREATE PROCEDURE MyProc

@.Date smalldatetime = NULL

AS

IF @.Date IS NULL SET @.Date=GetDate()

|||

ndinakar:

"You cannot assign non-deterministic value to a parameter in proc definition".

I knew about solution you offered, but real answer I was looking for is sentence I quoted.

Thank you.

default value of DateTime field - Now

Hello,
Can I simply set the default value of a dateTime field with the date and time that the record was created?yes, you can|||Use GetDate() function as default value...

default value in datetime column

hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function?

mp

If you go to Modify view of the table in either Enterprise Manager or SQL Server Management Studio and select that database field, there should be a property where you can specify the default value. Enter GETDATE() as the default value.|||

There are 2 ways to add default value as Today's Date

1) Using QueryAnalyzer

alter table <TableName>
add <ColumnName> DateTime default getdate()

//This will add one column with Default value as Today's date. If you want to add to existing column...2)

2) go to Enterprise Manager, ... , select your table, Right click, go to Design Table and open...

place the mouse cusor in which column you want to put default value, When you place the mouse cursor bottom of this you will see Columns Tab-->Default value TextBox put getdate() and Save . Now insert any row it will automatically enters to days date

|||

thanks, just what i needed!

Happy New Year!!!

mp

Default Value in Date Field

I am trying to put today's date into a DateTime field in a table. Is there
something I can put in the Default Value property of that field to do this?
I used to do this in Access, but can't figure it out in SQL Server.
Thanks in advance,
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemExample:
use northwind
go
create table dbo.t (
colA datetime not null constraint df_colA default (getdate())
)
go
insert into dbo.t default values
go
select * from dbo.t
go
drop table dbo.t
go
AMB
"chuckdfoster" wrote:

> I am trying to put today's date into a DateTime field in a table. Is ther
e
> something I can put in the Default Value property of that field to do this
?
> I used to do this in Access, but can't figure it out in SQL Server.
> Thanks in advance,
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||You can put GETDATE() in the Default Value property.
Jacco Schalkwijk
SQL Server MVP
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:u4jXEqZSFHA.1268@.TK2MSFTNGP14.phx.gbl...
>I am trying to put today's date into a DateTime field in a table. Is there
> something I can put in the Default Value property of that field to do
> this?
> I used to do this in Access, but can't figure it out in SQL Server.
> Thanks in advance,
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||create table mytable (
a int,
b smalldatetime default getdate()
)
insert into mytable (a) values (1)
select * from mytable
a | b
___________________
1 | 2005-04-25 15:36:00
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:u4jXEqZSFHA.1268@.TK2MSFTNGP14.phx.gbl...
>I am trying to put today's date into a DateTime field in a table. Is there
> something I can put in the Default Value property of that field to do
> this?
> I used to do this in Access, but can't figure it out in SQL Server.
> Thanks in advance,
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Hi
Use GETDATE() : For current systemdate and
GETUTCDATE() : for current GMT
Aneesh
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:u4jXEqZSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> I am trying to put today's date into a DateTime field in a table. Is
there
> something I can put in the Default Value property of that field to do
this?
> I used to do this in Access, but can't figure it out in SQL Server.
> Thanks in advance,
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>|||Thank you! Those where exactly what I needed!
Chuck Foster
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:%23g%23JEwZSFHA.2348@.TK2MSFTNGP09.phx.gbl...
> create table mytable (
> a int,
> b smalldatetime default getdate()
> )
> insert into mytable (a) values (1)
> select * from mytable
> a | b
> ___________________
> 1 | 2005-04-25 15:36:00
>
> "chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
> news:u4jXEqZSFHA.1268@.TK2MSFTNGP14.phx.gbl...
there
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.
try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegro ups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>