How can I convert 12/2/ to 12/2/current year

I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).

Can anyone help me with this?

Look up the DatePart function in sql server. It has pretty much what you need.

|||

DECLARE @.ddatetimeSET @.d='11/20/2007'SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END

To put this in a view, just remove the DECLARE and SET statements. Copy the code from CASE through END into your select statement, and replace @.d with the field name from your table.Optionally add ' AS MyNewField' after the END to give the column a name.|||

Motley,

Is it possible to use this in a udf so I can use it in other views?

I tried the following, but received and error:

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 14

Incorrect syntax near 'END'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)
RETURNSDATETIME
BEGIN

SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()
THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)
ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)

END
GO

|||

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGIN RETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END )ENDGO

|||

Motley,

I get a new error: maybe I'm have missed something

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 12

Incorrect syntax near ')'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGINRETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END)GO

|||

Sorry, I editted the above code, it should work now. It was missing an END.

|||

Motley,

Thanks very much for your help, please take the rest of the week off.

How can I change the default location of full text catalog?

Our current full text catalog default location is C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\FTData, we want change the default location to c:\data\MSSQL.5\FTData, so all the FT catalog will be created in this new location. Can anyone tell me how to do it?

Thanks

ShihChao

See SQL Server 2005 Books Online topic:

CREATE FULLTEXT CATALOG (Transact-SQL)

If you use T-SQL you can specify the path.

The topic states “IN PATH 'rootpath'

Is the root directory for the catalog. If rootpath is not specified, the new catalog will be located in the default directory specified at setup.”

Full-text catalogs must be created on a local hard disk drive associated with an instance of SQL Server. rootpath cannot be a relative path; it must reside on a drive on the same computer.

As you probably know you can specify the path when you use the UI. It does not default to any path.

|||

Also…you can use regedit at your own risk to change the default fulltext path

HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/MSSQL.x/MSSQLServer/FullTextDefaultPath – specify path

The restart SQL Server service

|||Thanks all all the replies

How can i assign customize data to sql report

hi folks,
i want to assign customize data to sql report. i have one record related to a customer. i have its current balance. i have to show in report that after specific months how much its amount will be.
i have to show each years amount that user will get. i am showing report in my aspx page. how can i assign this customize dataset to report

Good Morning or Good Evening!
I could be mising something based upon your post - but -
When you develop a report in REporting Services - you have to define a DataSet
The dataset can "grab" data from Sql and you can define a text string (Select whatever, count(1) from whatevertableyouwant)
The dataset could also execute a stored procedure and if you have this information available within your database a T-SQL stored proc ) can be executed via the dataset definition of reporting services - then -
you should have no problems in defining the report display elements
still not sure - but I think not so hard!
best regards.

Hosting DBO Nightmare going Live in 3 Days and counting

My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.

I've used a few sql scripts to change the owner using

Exec sp_changeobjectowner 'object', 'user'

and I've used

Grant Execute on object to user

These have changed ownership but the site still doesn't work

Does anyone know what to do to achieve this.

Any help greatly appreciated

Many Thanks

DuncanI hate to tell you, but our hosting operation does give our customers only DDLAdmin rights...

...and they have NO (!) problem creating entries belonging to the dbo user.

It is trivial. Just make sure you create them for the dbo user in the first place (i.e. EXPLICIT).|||Thanks Thona, this seems to set all the tables up fine but when I run dotnetnuke, it has a index out of bound error, I think this is happening because it is not creating any records in the table