"Jason Sauer" <jsauer@johnsoninv.com> wrote in message news:<40291b31$0$73080$a0465688@nnrp.fuse.net>...[color=blue]
> FUNCTION dbo.DateForGrouping is formatting @RowDate into an invalid date.
> When I run that code passing in @rowdate = "2/10/2004" the value of
> "datestring" becomes "10/2 /2004". Because the day has less than 2 digits
> the resulting strng has a space in it and cannot be converted. You could use
> this instead of the concatenation for better results:
>
> SET @datestring = CONVERT(char(10), @RowDate, 103)
>
> The day part will automatically be formatted with a leading zero when
> necessary. Then @datestring will be "10/02/2004". If you want the date
> returned as "02/10/2004" (mm/dd/yyyy) then use the parameter 101 instead of
> 103 in the CONVERT statement above.
>
> Hope this helps.
>
> "Philip Yale" <philipyale@btopenworld.com> wrote in message
> news:e9c86dcc.0402100738.654be152@posting.google.c om...[color=green]
> > A colleague of mine has a query which fails to run under SQLAgent
> > batch with the following error:
> >
> > The conversion of a char data type to a datetime data type resulted in
> > an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The
> > statement has been terminated. [SQLSTATE 01000] (Error 3621). The
> > step failed.
> >
> > He can run the same query sucessfully via query analyzer (i.e. no
> > errors, and it does what he wants)
> >
> > If I try to run the same query through Query Analyzer on my
> > workstation, I get a different error altogether:
> >
> > Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11
> > The conversion of a char data type to a datetime data type resulted in
> > an out-of-range datetime value.
> >
> > Any idea what might be causing these differences in behaviour
> > depending on how and/or where the query is run from?
> >
> > The (working) statement in question is:
> >
> > ================================================== ======================
> > insert into Summary_ReferrerSales
> > select DateCreated,
> > ReferrerID,
> > ReferrerIDCount,
> > PUIDCount,
> > ReferrerDescription,
> > 0 as TotalOrderValue,
> > 0 as TotalOrderLines
> > from vw_ReferrerPopularity
> >
> > Warning: Null value is eliminated by an aggregate or other SET
> > operation.
> >
> > (11996 row(s) affected)
> > ================================================== ====================
> > And the table / view / function definitions (I take no responsibility
> > for the view definition!) are:
> >
> > CREATE TABLE [Summary_ReferrerSales] (
> > [DateCreated] [datetime] NULL ,
> > [ReferrerID] [char] (2) NULL ,
> > [ReferrerIDCount] [int] NULL ,
> > [PUIDCount] [int] NULL ,
> > [ReferrerDescription] [nchar] (100) NULL ,
> > [TotalOrderValue] [numeric](18, 0) NULL ,
> > [TotalOrderLines] [numeric](18, 0) NULL
> > ) ON [PRIMARY]
> >
> >
> > CREATE VIEW dbo.vw_ReferrerPopularity
> > AS
> > SELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) AS
> > ReferrerIDCount,
> > COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,
> > dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)
> > AS DateCreated,
> > dbo.LogReferrerID.Referrer AS ReferrerID,
> > dbo.LookupReferrerID.ReferrerDescription
> > FROM dbo.LogReferrerID INNER JOIN dbo.LookupReferrerID
> > ON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerID
> > WHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-01
> > 00:00:00', 102))
> > GROUP BY dbo.LogReferrerID.Referrer,
> > dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),
> > dbo.LookupReferrerID.ReferrerDescription
> > HAVING (dbo.LogReferrerID.Referrer <> 'WS')
> > AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)
> > AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)
> > AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >
> > CONVERT(DATETIME, '2003-09-01 00:00:00', 102))
> > AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)
> > ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESC
> >
> >
> > CREATE TABLE [LogReferrerID] (
> > [LogReferrerID] [int] NULL ,
> > [Referrer] [varchar] (2) NULL ,
> > [DateUsed] [smalldatetime] NULL ,
> > [HTTPReferrer] [varchar] (1000) NULL ,
> > [TargetURL] [varchar] (1000) NULL ,
> > [QueryString] [varchar] (1000) NULL ,
> > [PUID] [varchar] (50) NULL
> > ) ON [PRIMARY]
> >
> >
> > CREATE TABLE [LookupReferrerID] (
> > [ReferrerID] [char] (2) NOT NULL ,
> > [ReferrerDescription] [varchar] (100) NOT NULL ,
> > CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED
> > (
> > [ReferrerID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> >
> >
> > CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)
> > RETURNS datetime AS
> > BEGIN
> >
> > declare @datestring char(10)
> >
> > set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +
> > cast(datepart(mm,@RowDate) as char(2)) + '/' +
> > cast(datepart(yyyy,@RowDate) as char(4))
> >
> > return cast(@datestring as datetime)
> > END[/color][/color]
Jason,
Many thanks for this - there appear to be some excellent suggestions.
I'll try them out and let you know what happens.
Cheers,
Phil
