FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?
Search the archives and you will find workarounds and the reason for this. One workaround is to
create a view in which you call getdate and use that view. But be aware that this makes getdate
non-atomic and non-deterministic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>
|||Also see http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?Search the archives and you will find workarounds and the reason for this. One workaround is to
create a view in which you call getdate and use that view. But be aware that this makes getdate
non-atomic and non-deterministic.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>|||Also see http://www.aspfaq.com/2439
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?Search the archives and you will find workarounds and the reason for this. O
ne workaround is to
create a view in which you call getdate and use that view. But be aware that
this makes getdate
non-atomic and non-deterministic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.ph
x.gbl...
> I have created my function call a getdate(). After running my function I g
et
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>|||Also see http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

Factorial Question

Hi, folks.
This might be irrelevant to the SQL, but I have a question regarding the factorial function.
n! = n(n-1)(n-2)(n-3)...(2)(1)
I would like to find the value of n when I know the value of n!.
Does anyone know how to do this?
TIA.Hi, folks. This might be irrelevant to the SQL, but I have a question regarding the factorial function.
n! = n(n-1)(n-2)(n-3)...(2)(1)
I would like to find the value of n when I know the value of n!.
Q1 Does anyone know how to do this?
TIA.

A1 All you need to do is loop through an iterative calculation (n times) you can implement as a stored procedure, a Sql Server 2k fn, or just run the tsql:

Declare
@.vn int,
@.vi int,
@.vFactorialResult BigInt

Select @.vn = 4
If @.vn < 21 And @.vn > 0
Begin
Select @.vi = 1, @.vFactorialResult = 1
-- Check term iteration (loop)
While @.vi <= @.vn
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
-- increment loop iteration counter
Select @.vi = @.vi + 1
End
Select @.vn As 'n:', @.vFactorialResult As 'FactorialResult:'
End
Else If @.vn >= 21
Begin
Select @.vn As 'n is too large:'
End
Else
Begin
Select @.vn As 'Error n is:'
End

-- As a Sql Server 2k fn:
Create Function dbo.fn_Factorial (@.pn int)
Returns BigInt
AS
Begin
Declare
@.vi int,
@.vFactorialResult BigInt

If @.pn > 21 Or @.pn < 0 Return -1
Select @.vi = 1, @.vFactorialResult = 1
-- Check term iteration (loop)
While @.vi <= @.pn
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
-- increment loop iteration counter
Select @.vi = @.vi + 1
End
Return @.vFactorialResult
End

-- To use fn_Factorial
Declare
@.vn int,
@.vFactorialResult BigInt
Select @.vn = 6
Select @.vFactorialResult = [master].[dbo].[fn_Factorial](@.vn)
Select @.vn As 'n:', @.vFactorialResult As 'FactorialResult:'|||Sorry, DBA.

I think my message was not clear.

I don't know the value of n.
I only have the value of n!

Is it possible to enter the value of n!, and get the value of n calculated as output?

TIA|||Originally posted by iamcrom
Sorry, DBA.

I think my message was not clear.

I don't know the value of n.
I only have the value of n!

Is it possible to enter the value of n!, and get the value of n calculated as output?

TIA

My mistake, it looked like a simple typographical error. For that you could implement some kind of factoring procedure or an estimation procedure.

For an estimation based proc it would simply start with a initial guess and converge until the result is either exactly matched e.g.(Factorial 7) or it is determined that the number cannot be a factorial product e.g.(6020). It should be relativly straight forward to implement, (you should have less than 21 possibilities to check), but more complex than the factorial. Finding an optimized algorithim could take a while (and some testing).|||Thank you, DBA.

The problem is that I will be choosing one number from 1 to 75, depending on the probability given. This is problematic because getting that probability relates to binomial coefficient, which in turn relates to factorial.

I will have probability as input, and spit the corresponding number (1 - 75).

My memory is hazy, but I remember seeing a formula to calculate n in high school. Does anyone remember that formula?

It is driving me crazy.|||If you may be certain the number is a factorial product, I think you could simply use a proc similar to the factorial (except altered to succesively examine the modulus result)?

In that case the last 0 modulo result sould be n (which you could check by running fn_Factorial (n)) For example:

Select 24%2, 24%3, 24%4, 24%5

The last 0 result is for 4;

The check 4! = 24

If the result doesn't check it wouldn't be a factorial product.|||Never mind that won't work.|||The following assumes that the value you submit is a valid factorial number - if this is not a guarantee then just do a > comparison and bail out once the value exceeds the supplied factorial.

Declare
@.vi int,
@.vFactorialResult float,
@.vControl float

select @.vControl = 'the n! value
select @.vFactorialResult = 1, @.vi = 1
while @.vi < 76
Begin
-- calculate Factorial Result term
Select @.vFactorialResult = @.vFactorialResult * @.vi
if @.vControl = @.vFactorialResult
begin
Select @.vi as 'n found', @.vFactorialResult as 'result', @.vControl as 'control'
break
end
-- increment loop iteration counter
Select @.vi = @.vi + 1
End|||RE: For an estimation based proc it would simply start with a initial guess and converge until the result is either exactly matched e.g.(Factorial 7) or it is determined that the number cannot be a factorial product e.g.(6020). It should be relativly straight forward to implement, (you should have less than 21 possibilities to check), but more complex than the factorial. Finding an optimized algorithim could take a while (and some testing).

The following should do it to the nearest non-overflow est. n! (I used bigint instead of floats, you could always rewrite it):

Facing problem with execute function

The execute function is not taking the arabic chars in..or nvarchars in

what i did is execute(@.sql)
where @.sql is a nvarchar

Assuming it as a insert statement to a table the chars are appearing as ?
where as the chars when inserted using a plain insert query comes clearly in arabic

set @.sql=insert into test values('arabic chars');
insert into test values('arabic chars'); works good plainly
but when called through execute it flops
execute (@.sql)
I even tried by inserting the @.sql value to the DB the query formed is shown that it is in arabic.

i tried using this but syntax error occured
EXECUTE sp_executesql @.stmt =N' (@.sqls)',
@.Farms = N'@.sqls nvarchar(max)',
@.sqls = @.strintest

The above is working only when we give the query

with dynamic values to be passed in

You have to ensure the value string shoule be prefixed with N.

(example) all the quires are working properly..

Code Snippet

insert Into testarabic values(N'???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????')

Exec ('insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')')

Declare @.SQL as NVarchar(1000);

Select @.SQL=N'insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')'

Exec (@.SQL)

The following quires will fail,

Code Snippet

insert Into testarabic values('???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????')

Exec ('insert Into testarabic values(''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')')

Declare @.SQL as Varchar(1000);

Select @.SQL=N'insert Into testarabic values(N''???? ????? ??????? ???? ??? ???? ?????. ????? ?????? ?? ?????? ?? ???? ??????'')'

Exec(@.SQL)

Extracting words from strings

I've done up this script which I'll turn into a user-defined function for
extracting words from text. However it seems a little over-complicated.
Anyone got any ideas of how to simplify it?
Thanks
Damien
SET NOCOUNT ON
DECLARE @.word TINYINT
DECLARE @.search_string VARCHAR( 2000 )
DECLARE @.delimiter CHAR( 1 )
-- Initialise variables
SET @.word = 1
SET @.search_string = 'This is a test function'
SET @.delimiter = ' '
SET @.search_string = @.search_string
DECLARE @.return VARCHAR( 25 )
DECLARE @.string CHAR( 1 )
DECLARE @.pos SMALLINT
DECLARE @.old_pos SMALLINT
DECLARE @.start SMALLINT
DECLARE @.total_len SMALLINT
DECLARE @.words SMALLINT
DECLARE @.len SMALLINT
-- Initialise variables
SET @.start = 1
SET @.old_pos = 1
SET @.pos = 1
SET @.words = 1
SET @.len = 0
SET @.total_len = LEN( @.search_string )
-- Check for errors
IF @.total_len = 0
BEGIN
RAISERROR( 'Invalid search string: %s.', 16, 1, @.search_string )
END
-- SELECT @.search_string AS '12345678901234'
-- Loop thru string one character at a time
WHILE @.pos <= @.total_len
BEGIN
SET @.string = SUBSTRING( @.search_string, @.pos, 1 )
-- Check we're not at the end of the string
IF @.word > @.words BREAK
IF @.pos = @.total_len
BEGIN
SET @.start = @.old_pos
SET @.len = @.pos - @.old_pos + 1
BREAK
END
-- Found a space; any previous text is the first word, any further text
is further word
IF @.string = @.delimiter
BEGIN
IF @.words = @.word
BEGIN
-- Current word is same as requested word
SET @.start = @.old_pos
SET @.len = @.pos - @.old_pos
BREAK
END
ELSE
BEGIN
SET @.old_pos = @.pos + 1
END
SET @.words = @.words + 1
END
-- Debuggin
--SELECT @.pos AS '@.pos', @.string AS '@.string', @.start AS '@.start',
@.old_pos AS '@.old_pos', @.word AS '@.word', @.words AS '@.words', @.len AS '@.len'
-- Increment current position
SET @.pos = @.pos + 1
END
-- Set return value
SET @.return = SUBSTRING( @.search_string, @.start, @.len )
function_exit:
-- RETURN @.return
SELECT @.return, LEN( @.return )
SET NOCOUNT OFFTry this....
if exists (select 'Y' from dbo.sysobjects where id =
object_id(N'[dbo].[fn_Split]'))
BEGIN
Drop Function fn_Split
END
GO
Create Function fn_Split
(
@.SplitString text,
@.Delimiter VARCHAR(1) = ' '
) RETURNS @.Dictionary TABLE (word varchar(8000)) AS
BEGIN
DECLARE
@.ParsedString VARCHAR(8000),
@.element VARCHAR(8000),
@.start_pos int,
@.end_pos int,
@.DataLength int,
@.BytesProcessed int,
@.length int
SELECT
@.BytesProcessed = 0,
@.DataLength = DataLength(@.SplitString)
SELECT @.ParsedString = Substring(@.SplitString, @.BytesProcessed + 1, 8000)
WHILE @.BytesProcessed < @.DataLength BEGIN
SELECT
@.BytesProcessed = @.BytesProcessed + DataLength(@.ParsedString)
SELECT
@.start_pos = 0
SELECT @.end_pos = CHARINDEX(@.Delimiter, @.ParsedString, @.start_pos + 1)
WHILE @.end_pos <> 0 BEGIN
SELECT @.length = (@.end_pos - @.start_pos) - 1
SELECT
@.element = SUBSTRING(@.ParsedString, @.start_pos + 1, @.length),
@.start_pos = @.end_pos
INSERT INTO @.Dictionary
VALUES (@.element)
SELECT @.end_pos = CHARINDEX(@.Delimiter, @.ParsedString, @.start_pos + 1)
END
SELECT @.ParsedString = SUBSTRING(@.ParsedString, @.start_pos + 1, 8000)
SELECT @.ParsedString = @.ParsedString + Substring(@.SplitString,
@.BytesProcessed + 1, 8000 - DataLength(@.ParsedString))
END
SELECT @.element = @.ParsedString
IF @.element IS NOT NULL
INSERT INTO @.Dictionary
VALUES (@.element)
RETURN
END
GO
select * from dbo.fn_Split('This is a test function',' ')|||I can't follow exactly what you're trying to do in that code but it looks
like you're trying to return the string of searched text from a string being
searched. For example, searching for "B" in "ABC".
In that case you could do something like:
DECLARE
@.string VARCHAR(10)
, @.search VARCHAR(10)
SELECT
@.string = 'ABC'
, @.search = 'B'
SELECT SUBSTRING( @.string, CHARINDEX(@.search, @.string), LEN(@.search) )
That code will return "B"; if you searched for "D" it would return an empty
string.
But doing that doesn't really make sense, because you already know the
string you're searching for. Couldn't you just use CHARINDEX(), and if it
returns > 0 reuse the string that is the text you're searching for?
Only caveat in all that is that it is not case sensitive for
case-insentitive collations.
"Damien" wrote:

> I've done up this script which I'll turn into a user-defined function for
> extracting words from text. However it seems a little over-complicated.
> Anyone got any ideas of how to simplify it?
> Thanks
>
> Damien
>
> SET NOCOUNT ON
> DECLARE @.word TINYINT
> DECLARE @.search_string VARCHAR( 2000 )
> DECLARE @.delimiter CHAR( 1 )
> -- Initialise variables
> SET @.word = 1
> SET @.search_string = 'This is a test function'
> SET @.delimiter = ' '
> SET @.search_string = @.search_string
> DECLARE @.return VARCHAR( 25 )
> DECLARE @.string CHAR( 1 )
> DECLARE @.pos SMALLINT
> DECLARE @.old_pos SMALLINT
> DECLARE @.start SMALLINT
> DECLARE @.total_len SMALLINT
> DECLARE @.words SMALLINT
> DECLARE @.len SMALLINT
> -- Initialise variables
> SET @.start = 1
> SET @.old_pos = 1
> SET @.pos = 1
> SET @.words = 1
> SET @.len = 0
> SET @.total_len = LEN( @.search_string )
> -- Check for errors
> IF @.total_len = 0
> BEGIN
> RAISERROR( 'Invalid search string: %s.', 16, 1, @.search_string )
> END
> -- SELECT @.search_string AS '12345678901234'
> -- Loop thru string one character at a time
> WHILE @.pos <= @.total_len
> BEGIN
> SET @.string = SUBSTRING( @.search_string, @.pos, 1 )
> -- Check we're not at the end of the string
> IF @.word > @.words BREAK
> IF @.pos = @.total_len
> BEGIN
> SET @.start = @.old_pos
> SET @.len = @.pos - @.old_pos + 1
> BREAK
> END
> -- Found a space; any previous text is the first word, any further tex
t
> is further word
> IF @.string = @.delimiter
> BEGIN
> IF @.words = @.word
> BEGIN
> -- Current word is same as requested word
> SET @.start = @.old_pos
> SET @.len = @.pos - @.old_pos
> BREAK
> END
> ELSE
> BEGIN
> SET @.old_pos = @.pos + 1
> END
> SET @.words = @.words + 1
> END
> -- Debuggin
> --SELECT @.pos AS '@.pos', @.string AS '@.string', @.start AS '@.start',
> @.old_pos AS '@.old_pos', @.word AS '@.word', @.words AS '@.words', @.len AS '@.le
n'
> -- Increment current position
> SET @.pos = @.pos + 1
> END
>
> -- Set return value
> SET @.return = SUBSTRING( @.search_string, @.start, @.len )
>
> function_exit:
> -- RETURN @.return
> SELECT @.return, LEN( @.return )
> SET NOCOUNT OFF
>|||Use a table of sequential numbers for this purpose instead of loop routines.
You can create one easily using the hack:
SELECT TOP 2000 IDENTITY ( INT ) n INTO Nbrs
FROM sysobjects s1, sysobjects s2 ;
Now your requirement is simpler:
DECLARE @.search_string VARCHAR( 2000 )
DECLARE @.delimiter CHAR( 1 )
SET @.search_string = 'This is a test function'
SET @.delimiter = ' '
SELECT SUBSTRING( @.search_string, n, CHARINDEX( @.delimiter,
@.search_string + @.delimiter, n ) - n )
FROM Nbrs
WHERE SUBSTRING( @.delimiter + @.search_string, n, 1 ) = @.delimiter
AND n < LEN( @.search_string ) + 1 ;
Anith

Extracting the date from a datetime value

Hi,
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:

>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:

> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:

> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>

Extracting Month Name from Date

Is there a function for getting the Month name from a date string? For
example, '01/20/2004' would return 'January'I think it's MonthName()
MonthName(getdate()) would return October
"Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> Is there a function for getting the Month name from a date string? For
> example, '01/20/2004' would return 'January'
>|||DATENAME(MONTH, GATDATE())
"Kelly" <kelly.hauser@.amerock.com> wrote in message
news:ectKTi#qEHA.2764@.TK2MSFTNGP11.phx.gbl...
> I think it's MonthName()
> MonthName(getdate()) would return October
>
> "Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
> message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> > Is there a function for getting the Month name from a date string? For
> > example, '01/20/2004' would return 'January'
> >
> >
>

Extracting data types

How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?Try:
select [name]
from systypes
AMB
"Alur" wrote:

> How is it possible to
> take out all data types ?
> Which procedure( function, view)
> should I use ?
>|||Also see sp_datatype_info in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:D4FEECA6-E828-4FE8-8DF6-BCD8BDD94E13@.microsoft.com...
How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?|||Thank you.|||Thank you.