April 30, 2012 at 7:09 am
While I convert old table to new table, I need update from one field (date type) to another field (varchar type) and first date of months as FILE_DATE no matter what date it is.
The example are as below:
DATE_FILLED FILE_DATE
----------------------------
2011-02-12 20110201
2011-10-21 20111001
2012-05-10 20120501
2012-11-12 20121101
How to code to run it?
April 30, 2012 at 7:21 am
Hi
Does this help?
DECLARE @Date DATE = '13-05-2012'
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)-1),@Date),112) AS Date_Value
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 30, 2012 at 7:22 am
Hi ,
Check the below functions they will help you...
CREATE FUNCTION [GetFirstDateofMonth]
(@Date as DateTime)
RETURNS DateTime AS
BEGIN
Declare @FirstDate DateTime
Set @FirstDate = DateAdd(Day, 1, @Date - Day(@Date) + 1) -1
RETURN @FirstDate
END
GO
CREATE FUNCTION [GetLastDateofMonth]
(@Date as DateTime)
RETURNS DateTime AS
BEGIN
Declare @LastDate DateTime
Set @LastDate = DateAdd(Month, 1, @Date - Day(@Date) + 1) -1
RETURN @LastDate
END
GO
April 30, 2012 at 7:24 am
These return DateTime the OP wanted Varchar :hehe:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 30, 2012 at 8:08 am
I create a function as below.
It is working but I do not think it is the best way.
create FUNCTION [dbo].[GetFirstDayOfMonth] ( @myDate DATE )
RETURNS varchar(20)
BEGIN
declare @tempdate varchar(20)
set @tempdate= convert(varchar(20), @mydate,101)
return right(@tempdate,4)+left(@tempdate,2)+'01'
END
April 30, 2012 at 8:14 am
Using a Function is not the best was as it will have a negative impact on query performance.
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 30, 2012 at 8:33 am
adonetok (4/30/2012)
I create a function as below.It is working but I do not think it is the best way.
create FUNCTION [dbo].[GetFirstDayOfMonth] ( @myDate DATE )
RETURNS varchar(20)
BEGIN
declare @tempdate varchar(20)
set @tempdate= convert(varchar(20), @mydate,101)
return right(@tempdate,4)+left(@tempdate,2)+'01'
END
Three things.
First, the scalar function defined above will kill your performance.
Second, unless you are writing a function that will be used in numerous procedures and/or quries, the following will give you better performance in your select column list:
convert(varch(8), dateadd(mm, datediff(mm,'19000101',DateFilled), '19000101'), 112) as FileDate
And if you are going to use a function, then it should be an inline table valued function:
create function dbo.GetFirstOfMonth(@ThisDate date)
returns table with schemabinding
as return (
select convert(varch(8), dateadd(mm, datediff(mm,'19000101',@ThisDate), '19000101'), 112) as FileDate
);
You then call this function in the FROM clause using the CROSS APPLY operator.
For mor information on this please read the following blog entry: http://qa.sqlservercentral.com/blogs/lynnpettis/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply