Money Data Type: function

Showing posts with label function. Show all posts

Friday, March 30, 2012

More ntext trouble

I'm trying to build a REPLACE() function for NTEXT values.
I'm facing a lot of trouble doing it, and I think it could be a very common problem.
I think the biggest problem is when one READTEXT a chunk of a NTEXT value, I couldn't find any way to put that chunk into an NVARCHAR in order to postprocess it.
Let me show you some source code:

BEGIN TRAN
DECLARE @.ptrval varbinary(16)
DECLARE @.datalen integer
DECLARE @.chunk nvarchar(4000)
DECLARE @.i integer
DECLARE @.q integer
DECLARE @.maxlenvarchar integer

-- LET THE POINTER @.ptrval POINTS TO THE NTEXT VALUE, GATHER THE REAL LENGTH INTO @.datalen (300000 bytes approx.)
SELECT @.ptrval = TEXTPTR(ntext_value), @.datalen = DATALENGTH(ntext_value)/2
FROM my_table
WHERE id = 8293

SELECT @.i = 0, @.maxlenvarchar = 4000

-- I WANT TO REPLACE ALL OCCURRENCIES OF CHARACTER 'A' WITH CHARACTER 'B'
-- INTO THE NTEXT FIELD. SO, I THOUGHT THAT IT COULD BE POSSIBLE DOING IT THIS WAY:
-- 1) READ THE NTEXT VALUE IN CHUNKS OF 4000 BYTES
-- 2) PUT EACH CHUNK INTO AN NVARCHAR VARIABLE (@.chunk)
-- 3) APPLY THE REPLACE FUNCTION TO EACH @.chunk
-- 4) REBUILD THE NTEXT VALUE AND STORE IT AGAIN INTO THE TABLE

-- HERE, IN THE WHILE LOOP IS PART OF THE JOB, I COULDN'T GO BEYOND THIS
WHILE @.datalen > 0
BEGIN
IF @.datalen > @.maxlenvarchar
SELECT @.q = @.maxlenvarchar
ELSE
SELECT @.q = @.datalen

-- THIS COMMAND READS 4000 BYTES OF THE NTEXT VALUE,..
-- BUT HOW CAN I STORE IT INTO A NVARCHAR IN ORDER TO USE REPLACE() ??
READTEXT my_table.ntext_value @.ptrval @.i @.q

SELECT @.i = @.i + @.maxlenvarchar, @.datalen = @.datalen - @.q

-- I DON'T KNOW HOW TO "PRINT" THE CHUNK, THIS COMMAND PRINTS THE POINTER VALUE (HEX)
PRINT @.ptrval

-- I CAN SEE THE TEXT POINTER IS ALWAYS VALID
PRINT TEXTVALID ('my_table.ntext_value', @.ptrval)
END

COMMIT

-- WHAT I HAVEN'T SEE YET IS THE PART OF REBUILDING THE NTEXT VAL AND WRITE IT AGAIN INTO THE DATABASE.Have you tried using the PATINDEX and UPDATETEXT functions ?|||I've read about them ... but I cannot imagine in which way I should use them in order to do what I want.|||Here you go:

code example (http://www.1aspstreet.com/xq/ASP/txtCodeId.395/lngWid.5/qx/vb/scripts/ShowCode.htm)|||/*
SP on WWW page linked in rnealejr's message would process only a table with one record.
It also "rereplace".
*/

/* DESCRIPTION
Replacing is thought to be very simple, but this is not right in blobs.

I use overlaying SUBSTRING frame of 4000 chars
sliding by (4000+1-len(@.OldStr))
-> 4000 chars sure
Cursor *MUST* be used, because of statement UPDATETEXT,
SQL pseudoprocedure, which does not have FROM part
and needs poiter to blob, unique for each blob column and table row.

Temp tables:
#BlobTable - table with Blobs (to be updated)
#BlobPos - current pos to be sure not to "rereplace" 'A'->'AA' ('XXAXX'->'XXAAAAAAAAAAAA...XX')
#BlobRes - results of single find
#BlobUpd - list for blob updates

Beware of replacing data with table option 'text in row' set !!!
4000 limit is for nvarchar(international,UNICODE), use 8000 varchar for single language.

*/

--INITIALIZATION (creating temp tables, generating some 16kB blob data)
set nocount on
set textsize 2147483647
if object_id('tempdb..#BlobTable') is not null drop table #BlobTable
if object_id('tempdb..#BlobPos') is not null drop table #BlobPos
if object_id('tempdb..#BlobRes') is not null drop table #BlobRes
if object_id('tempdb..#BlobUpd') is not null drop table #BlobUpd
GO
create table #BlobTable (
id int identity(1,1) primary key
,Blob ntext not null
,BlobCopy ntext not null
)
create table #BlobPos (
id int primary key
,ptr varbinary(16) not null
,BlobLen int not null
,BlobPos int not null
,BlobDelta int not null
)
create table #BlobRes (
id int primary key
,Pos int not null
)
create table #BlobUpd (
id int not null
,Pos int not null
)
GO
--generating test data (10 different blobs, about 80000 chars each)
declare @.ptr varbinary(16)
declare @.ptrCopy varbinary(16)
declare @.index int
declare @.addtext nvarchar(4000)
declare @.Counter int
set @.addtext=replicate('X',4000)
while 10>(select count(*) from #BlobTable) begin
insert #BlobTable(Blob,BlobCopy)
select
replicate('X',count(*))+'abcdefghijklmabcdefghijkl m'
,replicate('X',count(*))+'abcdefghijklmabcdefghijk lm'
from #BlobTable
select @.ptr=textptr(Blob),@.index=datalength(Blob)/2,@.ptrCopy=textptr(BlobCopy)
from #BlobTable
where id=SCOPE_IDENTITY()
set @.Counter=0
while @.Counter<20 begin
set @.index=@.index
updatetext #BlobTable.Blob @.ptr @.index 0 @.addtext
set @.Counter=@.Counter+1
end
end
update #BlobTable set
BlobCopy=Blob
GO
--INITIALIZATION-END

--REPLACE ('efgh' WITH 'efgh?')
declare @.OldStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.OldStrLike nvarchar(4000)
declare @.length int
declare @.lengthDelta int
declare @.ptr varbinary(16)
declare @.NewStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.index int
declare @.c cursor
--init of replace
set @.OldStr='efgh'
set @.NewStr='efgh?'
set @.lengthDelta=len(@.NewStr)-len(@.OldStr)
set @.length=len(@.OldStr)
insert #BlobPos(id,ptr,BlobLen,BlobPos,BlobDelta)
select id,ptr=textptr(Blob),BlobLen=datalength(Blob)/2, BlobPos=0,BlobDelta=0
from #BlobTable
--loop of finding
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
while @.@.rowcount>0 begin
insert #BlobUpd(id,Pos)
select br.id,bp.BlobPos+br.Pos-1+bp.BlobDelta
from #BlobRes br
join #BlobPos bp
on br.id=bp.id and br.Pos>0
update bp set
bp.BlobPos=bp.BlobPos+case when br.Pos>0 then br.Pos else 4000+1-len(@.OldStr) end
,bp.BlobDelta=bp.BlobDelta+case when br.Pos>0 then @.lengthDelta else 0 end
from #BlobPos bp
join #BlobRes br
on bp.id=br.id
delete #BlobRes
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
end
--update of blob by list
set @.c=cursor local forward_only static for
select bu.Pos,bp.ptr
from #BlobUpd bu
join #BlobPos bp
on bu.id=bp.id
open @.c
fetch next from @.c into @.index,@.ptr
while @.@.fetch_status=0 begin
updatetext #BlobTable.Blob @.ptr @.index @.length @.NewStr
fetch next from @.c into @.index,@.ptr
end
close @.c
deallocate @.c
GO
--END OF REPLACE

--FINALIZE
set nocount off
select * from #BlobTable
drop table #BlobTable
GO

/*
This algorithm is really slow, especially on large blobs.
I found many fast optimalizations for this almost simple algorithm,
but I realized that I am not so much interested in blobs.
I should spend a week of freetime or more to merge ideas and optimize, too much for fun.
*/

Wednesday, March 28, 2012

More errors in convert function

In hijri calender any month can be 30 or 29 day
in convert function
if I write
select convert (datetime ,'29-12-1426,131) it is ok
this year 12 month is 30 days
but if I write
select convert (datetime ,'30-12-1426,131)
ther is an error

Thanks for any help

Tamer229,
There are many variations of the Islamic calendar. In some versions,
there are 29 days in the month Thou Alhajja in the year 1426, and in
others there are 30 days. There are only 29 days in this month
according to the Islamic calendar version used by Microsoft, which
is why you are getting an error when you try to convert the 30th day
of this month. In the Microsoft version, the western date January
30, 2006 is the first day of Muharram, 1427. In other versions of
the Islamic calendar, January 30, 2006 is the 30th day of Thou Alhajja,
1426.
See http://www.phys.uu.nl/~vgent/islam/islamyear_en.htm
for a calculator that shows several versions. The version used
by Microsoft is labeled "Ia [15, astronomical = “Kuwaiti algorithm”]"
Steve Kass
Drew University

Monday, March 26, 2012

Month-to-month function

Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:

@.CurrentDate = 9/10/2007

@.EndDate = last working day 9/30/2007

Total# of working days = 15

October:

@.CurrentDate = 10/1/2007

@.EndDate = last working day 10/31/2007

Total# of working days = 23

November:

@.CurrentDate = 11/1/2007

@.EndDate = last working day 11/30/2007

Total# of working days = 22

etc.

Any ideas of how i can approch this?

Thanks in advance.

If we just count out weekends, you can use this script. This will not take into account holidays.

Code Snippet

DECLARE @.startDate DATETIME,

@.dateTest DATETIME,

@.workDays INT

SET @.startDate = getDate()

SET @.dateTest = @.startDate

SET @.workDays = 0

WHILE( MONTH(@.startDate) = MONTH(@.dateTest) )

BEGIN

IF( DATENAME(dw, @.dateTest) != 'SATURDAY' AND DATENAME(dw, @.dateTest) != 'Sunday')

SET @.workDays = @.workDays + 1

SET @.dateTest = @.dateTest + 1

END

SELECT @.workDays

|||

You might try to search this forum on "Working Days". Also, give a look to this article about using a "calendar table:"

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

Code Snippet

create function udf_WeekdayCounter

(

@.dtFrom datetime

, @.dtThrough Datetime

)

returns smallint

begin

if @.dtThrough <= @.dtFrom

return 0

declare @.iCounter int

set @.iCounter = 0

declare @.dtFrom datetime, @.dtThrough datetime

set @.dtFrom = '11/1/2007'

set @.dtThrough = '11/30/2007'

while @.dtFrom < = @.dtThrough

begin

if datepart(weekday,@.dtFrom) not in (datepart(weekday,'December 30, 2006'), datepart(weekday,'December 31, 2006'))

set @.iCounter = @.iCounter + 1

set @.dtFrom = dateadd(d, 1, @.dtFrom)

end

return @.iCounter

end

select dbo.udf_WeekdayCounter( '11/1/2007', '11/30/2007')

sql

Friday, March 23, 2012

Monthname in SQL server

Being new to SQL server T-SQL (working with DB2 / ORACLE) is there a function like monthname() in SQL server. I.e. a function that takes date as input and returns the Monthname (like AUG or AUGUST)??This example extracts the month name from the date returned by GETDATE.

SELECT DATENAME(month, getdate()) AS 'Month Name'

Here is the result set:

Month Name
----------
February|||Sorry, where can I put the date where I can extract the monthname from?
I want to add an object that displays 'JAN' or Januari if I have a date like:
01/01/2003|||SELECT DATENAME(month, '01/01/2003') AS 'Month Name'|||Thank you very much..............

MonthName function in IIF

Hi,
We are getting a runtime #error when using MonthName in an IIF function more
than once as follows:
IIF(Parameters!ReportPeriod.Value <
7,MonthName(Parameters!ReportPeriod.Value +
6),MonthName(Parameters!ReportPeriod.Value))
Parameters!ReportPeriod is an integer, and the expression works Ok until the
second MonthName is added.
Thanks,
MattDoes this work (by adding an explicit cast)?
= IIF(Parameters!ReportPeriod.Value > 7,
MonthName(Parameters!ReportPeriod.Value + 6),
MonthName(CInt(Parameters!ReportPeriod.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> Hi,
> We are getting a runtime #error when using MonthName in an IIF function
> more
> than once as follows:
> IIF(Parameters!ReportPeriod.Value <
> 7,MonthName(Parameters!ReportPeriod.Value +
> 6),MonthName(Parameters!ReportPeriod.Value))
> Parameters!ReportPeriod is an integer, and the expression works Ok until
> the
> second MonthName is added.
> Thanks,
> Matt|||Thanks - that works Ok.
"Robert Bruckner [MSFT]" wrote:
> Does this work (by adding an explicit cast)?
> = IIF(Parameters!ReportPeriod.Value > 7,
> MonthName(Parameters!ReportPeriod.Value + 6),
> MonthName(CInt(Parameters!ReportPeriod.Value)))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> > Hi,
> > We are getting a runtime #error when using MonthName in an IIF function
> > more
> > than once as follows:
> >
> > IIF(Parameters!ReportPeriod.Value <
> > 7,MonthName(Parameters!ReportPeriod.Value +
> > 6),MonthName(Parameters!ReportPeriod.Value))
> >
> > Parameters!ReportPeriod is an integer, and the expression works Ok until
> > the
> > second MonthName is added.
> >
> > Thanks,
> > Matt
>
>sql

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQL
Use the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...[vbcol=seagreen]
be
>
>.
>
|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>
|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101
280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
be[vbcol=seagreen]
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be
>

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter
>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
be
>> an equivelent in TSQL
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
--
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J
>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
>be
>> an equivelent in TSQL
>>
>>.
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>>how do i return the month in text from a number?
>>the vb function is monthname(), there does not seem to
> be
>>an equivelent in TSQL
>>
>>.

Month Function - left pad single digit months

Month(now()) returns 1 in January. How do I left pad that 1 such that
it will return 01 for all single digit months?
Thanks.If you need an expression in report design, try this:
=IIF(Month(Now())<10,"0" & Month(Now()),"" & Month(Now()))
Or, even simple:
=Right("0" & Month(Now()),2)
"Scott" <SHBOSTON@.gmail.com> wrote in message
news:77e80f94-f3bf-49fc-a8ec-d4c526066713@.v17g2000hsa.googlegroups.com...
> Month(now()) returns 1 in January. How do I left pad that 1 such that
> it will return 01 for all single digit months?
> Thanks.

Friday, March 30, 2012

More Partition Function and Partition Scheme questions.

More Partition Function and Partition Scheme questions.

More Partition Function and Partition Scheme questions.

More ntext trouble

Wednesday, March 28, 2012

More errors in convert function

Monday, March 26, 2012

Month-to-month function

Friday, March 23, 2012

Monthname in SQL server

MonthName function in IIF

Month name

Month name

Month name

Month Function - left pad single digit months

Money Data Type

Blog Archive

About Me