NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.

Non-deterministic UDF's?

Hello all, i want to make a non-deterministic UDF... is this possible?
Heres what i have... its an online catalog. The site has an option
DAYS_LIST_AS_NEW, where the owner can specify a number of
days for which a product is listed as new, so if the user puts a value of
30, products are listed on the "New Items" page for 30 days after the
value stored in their CREATEDATE field.
The product records also have a bit field LIST_AS_NEW which can
be used to override the time-calculation, and list a product as new even
after the x-day period has gone by.
I want to create a function that basically does whats below.
Thanks in advance for any help.
(code may not be perfect here.)
========================================
==============
FUNCTION getItemIsNew(@.ITEMID INT)
RETURNS BIT
BEGIN
DECLARE @.ALWAYS
SELECT @.ALWAYS= LIST_AS_NEW FROM PRODUCTS
WHERE ROWID = @.ITEMID
IF @.ALWAYS= 1
RETURN 1
DECLARE @.DAYS INT
SELECT @.DAYS = VAL FROM WEB_OPTS
WHERE OPT = 'DAYS_ON_NEW_LIST'
DECLARE @.DAYSGONE INT
SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, GETDATE())
FROM PRODUCTS WHERE ROWID = @.ITEMID
IF @.DAYSGONE > @.DAYS
RETURN 0
ELSE
RETURN 1
ENDGetdate() is not allowed in UDF. You could create a view and call it.
e.g.
create view get_date
as
select dt=getdate()
go
SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, (select dt from get_date))
FROM PRODUCTS WHERE ROWID = @.ITEMID
-oj
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:uhkK6$VxFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hello all, i want to make a non-deterministic UDF... is this possible?
> Heres what i have... its an online catalog. The site has an option
> DAYS_LIST_AS_NEW, where the owner can specify a number of
> days for which a product is listed as new, so if the user puts a value of
> 30, products are listed on the "New Items" page for 30 days after the
> value stored in their CREATEDATE field.
> The product records also have a bit field LIST_AS_NEW which can
> be used to override the time-calculation, and list a product as new even
> after the x-day period has gone by.
> I want to create a function that basically does whats below.
> Thanks in advance for any help.
> (code may not be perfect here.)
> ========================================
==============
> FUNCTION getItemIsNew(@.ITEMID INT)
> RETURNS BIT
> BEGIN
> DECLARE @.ALWAYS
> SELECT @.ALWAYS= LIST_AS_NEW FROM PRODUCTS
> WHERE ROWID = @.ITEMID
> IF @.ALWAYS= 1
> RETURN 1
> DECLARE @.DAYS INT
> SELECT @.DAYS = VAL FROM WEB_OPTS
> WHERE OPT = 'DAYS_ON_NEW_LIST'
> DECLARE @.DAYSGONE INT
> SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, GETDATE())
> FROM PRODUCTS WHERE ROWID = @.ITEMID
> IF @.DAYSGONE > @.DAYS
> RETURN 0
> ELSE
> RETURN 1
> END
>|||Yah, i saw a site with an example like that, but thought maybe
someone might have some more natural way to do this.
Anyone know is SS2K5 will support non-deterministic UDF's?
It really is a pretty cumbersome restriction.
Thanks in advance.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O3bX2TWxFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Getdate() is not allowed in UDF. You could create a view and call it.
> e.g.
> create view get_date
> as
> select dt=getdate()
> go
>
> SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, (select dt from get_date))
> FROM PRODUCTS WHERE ROWID = @.ITEMID
>
> --
> -oj
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:uhkK6$VxFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Some restrictions are removed in SQL Server 2005.
It makes it a lot harder to architect and non-deterministic functions as
each call to the function may return a different value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:uNVvk3WxFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Yah, i saw a site with an example like that, but thought maybe
> someone might have some more natural way to do this.
> Anyone know is SS2K5 will support non-deterministic UDF's?
> It really is a pretty cumbersome restriction.
> Thanks in advance.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O3bX2TWxFHA.2728@.TK2MSFTNGP14.phx.gbl...
>|||Reading the value from a view can end up having a serious impact on
performance. Consider supplying the datatime value through a parameter.
ML|||Thanks for the info,
Good to know well be able to do this.
It doesnt really make it any harder to program though
with non-det. udfs. I started my programming life on
ORACLE, where n.d. udfs are perfectly natural, and
it doesnt pose any problems.
The only time i remember it ever raising an issue was
if i wanted to create a function-based index (another
thing i miss in SS). But then you just have to carefully
code it so it is deterministic and you can also add a
compiler hint to tell the db that it is, which improves
performance then.
Sometimes that what you *want* to design...
non-determinism, its the whole point of the
function in some instances.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OfWL%23yYxFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi
> Some restrictions are removed in SQL Server 2005.
> It makes it a lot harder to architect and non-deterministic functions as
> each call to the function may return a different value.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:uNVvk3WxFHA.3556@.TK2MSFTNGP12.phx.gbl...
>|||When would you ever want to design a non-deterministic function? If you
are doing set operations and return multiple rows, then it really makes
no sense, because basically you would really be asking for unreliable
results.
If the goal is to get unreliable results (for example a custom random
function), then I can understand it.
Please note, that if you pass in a timestamp, your UDF would probably
still be deterministic.
If you are planning to use the function on a one-row-at-a-time basis,
then you could just as well use a stored procedure (with output
parameters if needed) instead of a UDF.
So my question is: what application would you have for a non-determistic
UDF?
Gert-Jan
Arthur Dent wrote:
> Thanks for the info,
> Good to know well be able to do this.
> It doesnt really make it any harder to program though
> with non-det. udfs. I started my programming life on
> ORACLE, where n.d. udfs are perfectly natural, and
> it doesnt pose any problems.
> The only time i remember it ever raising an issue was
> if i wanted to create a function-based index (another
> thing i miss in SS). But then you just have to carefully
> code it so it is deterministic and you can also add a
> compiler hint to tell the db that it is, which improves
> performance then.
> Sometimes that what you *want* to design...
> non-determinism, its the whole point of the
> function in some instances.
[snip]

Non-Deterministic function in DPV

Hi! Using the new SQL 2005 functionality where the optimizer determines
where the data is based on the check constraints defined on the tables, can
you use a non-deterministic function such as getdate() in the check
constraints as long as you have another mechanism to make certain the data
gets moved when the date is no longer in the valid range.
For example: Table 1 has data that is 12 months old or less. Table 2 has
data that is 13 months old or more.
Thanks.alw (alw@.discussions.microsoft.com) writes:
> Hi! Using the new SQL 2005 functionality where the optimizer determines
> where the data is based on the check constraints defined on the tables,
> can you use a non-deterministic function such as getdate() in the check
> constraints as long as you have another mechanism to make certain the
> data gets moved when the date is no longer in the valid range.
> For example: Table 1 has data that is 12 months old or less. Table 2 has
> data that is 13 months old or more.
No. It simply does not make any sense. SQL Server cannot trust you to
actually use that mechanism. If you want to move data daily, then you
have to redefine the constraints daily. Not to talk about if your datetime
column also has minutes and milliseconds...
Note that in SQL 2005 you also have partitioned tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Non-deterministic Clustered Index for Indexed View

Hi all,
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:

> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:

> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:

> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:

> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s