I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS KhorKhor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khor
Showing posts with label below. Show all posts
Showing posts with label below. Show all posts
Thursday, March 29, 2012
DB backup job for DB maintenance plan failed
I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS Khor
Khor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khor
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS Khor
Khor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khor
DB backup job for DB maintenance plan failed
I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS KhorKhor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khorsql
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS KhorKhor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khorsql
Thursday, March 22, 2012
DateTime Update Statement
I've looked through books online and can't understand why the below statement doesn't work:
update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'
The error returned is:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.
Thanks,
BrentOriginally posted by baolive
I've looked through books online and can't understand why the below statement doesn't work:
update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'
The error returned is:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.
Thanks,
Brent
What's the name of Month 27?|||Cut and paste this:
(Do code tags work here?)
USE Northwind
GO
CREATE TABLE tblregionalmarketrate (effectivedate datetime)
GO
INSERT INTO tblregionalmarketrate (effectivedate)
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate()
GO
SELECT * FROM tblregionalmarketrate
GO
UPDATE tblregionalmarketrate
SET effectivedate = '2003-01-27 00:00:00.000'
WHERE effectivedate > '2003-01-01 00:00:00.000'
GO
SELECT * FROM tblregionalmarketrate
GO
DROP TABLE tblregionalmarketrate
GO|||Thanks, see told you it would be simple, just had the month and day backwards. Could have sworn I tried it with the correct format before.
update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'
The error returned is:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.
Thanks,
BrentOriginally posted by baolive
I've looked through books online and can't understand why the below statement doesn't work:
update tblregionalmarketrate
set effectivedate = '2003-27-01 00:00:00.000'
where effectivedate > '2003-01-01 00:00:00.000'
The error returned is:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.
Thanks,
Brent
What's the name of Month 27?|||Cut and paste this:
(Do code tags work here?)
USE Northwind
GO
CREATE TABLE tblregionalmarketrate (effectivedate datetime)
GO
INSERT INTO tblregionalmarketrate (effectivedate)
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate() UNION ALL
SELECT GetDate()
GO
SELECT * FROM tblregionalmarketrate
GO
UPDATE tblregionalmarketrate
SET effectivedate = '2003-01-27 00:00:00.000'
WHERE effectivedate > '2003-01-01 00:00:00.000'
GO
SELECT * FROM tblregionalmarketrate
GO
DROP TABLE tblregionalmarketrate
GO|||Thanks, see told you it would be simple, just had the month and day backwards. Could have sworn I tried it with the correct format before.
Wednesday, March 21, 2012
Datetime Query
i am trying to query a datetime column in a db.
e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.dateSpecify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.|||Do i specify the date as a i wrote in the query. since the datetime is like
3/7/2005 4:24:01 AM ?
or do i have to declare the datime if it was today and use the variable in the query.|||Well, it depends what you're trying to achieve. :) If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:
CODE:
DECLARE @.dates TABLE(date1 DATETIME)
INSERT @.dates(date1)
SELECT '01/01/05 13:58:01.000' UNION ALL
SELECT '01/02/05 00:00:00.000' UNION ALL
SELECT '01/02/05 00:00:00.001' UNION ALL
SELECT '01/02/05 23:59:59.999' UNION ALL
SELECT '01/03/05 00:00:00.000' UNION ALL
SELECT '01/03/05 00:00:00.001' UNION ALL
SELECT '01/04/05 10:00:00.001'
SELECT date1 FROM @.dates
SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'
SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'
BOL Quote:
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990
Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.sql
e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.dateSpecify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.|||Do i specify the date as a i wrote in the query. since the datetime is like
3/7/2005 4:24:01 AM ?
or do i have to declare the datime if it was today and use the variable in the query.|||Well, it depends what you're trying to achieve. :) If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:
CODE:
DECLARE @.dates TABLE(date1 DATETIME)
INSERT @.dates(date1)
SELECT '01/01/05 13:58:01.000' UNION ALL
SELECT '01/02/05 00:00:00.000' UNION ALL
SELECT '01/02/05 00:00:00.001' UNION ALL
SELECT '01/02/05 23:59:59.999' UNION ALL
SELECT '01/03/05 00:00:00.000' UNION ALL
SELECT '01/03/05 00:00:00.001' UNION ALL
SELECT '01/04/05 10:00:00.001'
SELECT date1 FROM @.dates
SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'
SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'
BOL Quote:
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990
Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.sql
DateTime Problem in SP
I am getting this error in the SP shown below and don't see what is wrong?
Syntax error converting character string to smalldatetime data type.
The complete output is as follows:
---
DECLARE @.RC int
DECLARE @.Class char(2)
DECLARE @.StartDate datetime
DECLARE @.EndDate datetime
DECLARE @.Period varchar(10)
SELECT @.Class = 'SW'
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
SELECT @.Period = 'Test'
EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
@.Period
DECLARE @.PrnLine nvarchar(4000)
PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
PRINT @.PrnLine
---
=============== SP Code ================
@.Class As char(2),
@.StartDate As SmallDateTime,
@.EndDate As SmallDateTime,
@.Period As Varchar(10)
As
Set NOCOUNT ON
DECLARE
@.strSQL As varchar(1000)
SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
''' + Cast(@.EndDate As SmallDateTime) + ''
Print @.strSQL
INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
exec(@.strSQL)Maybe your system is set up for UK English, or some other regional settings,
or some other language.
How about we try a sensible and unambiguous date format, like YYYYMMDD.
SELECT @.startDate = '20050205', @.endDate = '20050213'
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:O9NBn5OKFHA.3420@.tk2msftngp13.phx.gbl...
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>|||Well for one thing You have "=>" In there, and that's wrong, it should be
">=".
Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
month... depending on server settings... A format that always works is
CCYYMMDD, or, for Feb 13, 2005,
'20050213'
try changing the string literals
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
to
SELECT @.StartDate = '20050205'
SELECT @.EndDate = '20050213'
and see if it works then...
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Wayne Wengert wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Your WHERE clause should be like this:
WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
Since you've already declared the parameters @.StartDate & @.EndDate as
SmallDateTime data types you don't have to do it again w/ the Cast()
function. What you have to do, since you're putting the date values in
a string, is convert them to string data types. In my example I used
CHAR(8) to just get a date like this '20040314'.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
Fnny1XBrRp8n15Q7xe4Mm6VR
=GT97
--END PGP SIGNATURE--|||And Oh, replace "=>" and "=<" with ">=", and "<="
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Thanks - I figured that out (finally)
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:849DC6A0-15EF-4616-BC47-555F941C4199@.microsoft.com...
> Well for one thing You have "=>" In there, and that's wrong, it should
be
> ">=".
> Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
> month... depending on server settings... A format that always works is
> CCYYMMDD, or, for Feb 13, 2005,
> '20050213'
> try changing the string literals
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> to
> SELECT @.StartDate = '20050205'
> SELECT @.EndDate = '20050213'
> and see if it works then...
>
> "Wayne Wengert" wrote:
>
wrong?
WHERE
=<|||Thanks - that was what I forgot!
Wayne
"MGFoster" <me@.privacy.com> wrote in message
news:6tpZd.10908$cN6.9661@.newsread1.news.pas.earthlink.net...
> Wayne Wengert wrote:
wrong?
WHERE
=<
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
> function. What you have to do, since you're putting the date values in
> a string, is convert them to string data types. In my example I used
> CHAR(8) to just get a date like this '20040314'.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
> Fnny1XBrRp8n15Q7xe4Mm6VR
> =GT97
> --END PGP SIGNATURE--|||
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
This still could lead to an error if his settings are, say, UK English, and
he says
SET @.startDate = '2/16/2005'
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
Neither do you have to do a CONVERT at all in this case (if he uses YYYYMMDD
in his SET/SELECT then it's already in 112 format), and nor do you have to
surround the date value with strings like you did. This will be sufficient:
WHERE ShowDate >= @.startDate
Finally, more for Wayne than the others, be careful how you define the end
date. If you say <= <somedate_notime> you will include rows with a value of
midnight on that day, but not 12:01 AM or 3:45 PM. If you only have
midnight timestamps in the data then it's no big deal, but if you don't
constrain the data, you're better off using < (@.endDate + 1).
A
Syntax error converting character string to smalldatetime data type.
The complete output is as follows:
---
DECLARE @.RC int
DECLARE @.Class char(2)
DECLARE @.StartDate datetime
DECLARE @.EndDate datetime
DECLARE @.Period varchar(10)
SELECT @.Class = 'SW'
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
SELECT @.Period = 'Test'
EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
@.Period
DECLARE @.PrnLine nvarchar(4000)
PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
PRINT @.PrnLine
---
=============== SP Code ================
@.Class As char(2),
@.StartDate As SmallDateTime,
@.EndDate As SmallDateTime,
@.Period As Varchar(10)
As
Set NOCOUNT ON
DECLARE
@.strSQL As varchar(1000)
SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
''' + Cast(@.EndDate As SmallDateTime) + ''
Print @.strSQL
INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
exec(@.strSQL)Maybe your system is set up for UK English, or some other regional settings,
or some other language.
How about we try a sensible and unambiguous date format, like YYYYMMDD.
SELECT @.startDate = '20050205', @.endDate = '20050213'
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:O9NBn5OKFHA.3420@.tk2msftngp13.phx.gbl...
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>|||Well for one thing You have "=>" In there, and that's wrong, it should be
">=".
Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
month... depending on server settings... A format that always works is
CCYYMMDD, or, for Feb 13, 2005,
'20050213'
try changing the string literals
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
to
SELECT @.StartDate = '20050205'
SELECT @.EndDate = '20050213'
and see if it works then...
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Wayne Wengert wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Your WHERE clause should be like this:
WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
Since you've already declared the parameters @.StartDate & @.EndDate as
SmallDateTime data types you don't have to do it again w/ the Cast()
function. What you have to do, since you're putting the date values in
a string, is convert them to string data types. In my example I used
CHAR(8) to just get a date like this '20040314'.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
Fnny1XBrRp8n15Q7xe4Mm6VR
=GT97
--END PGP SIGNATURE--|||And Oh, replace "=>" and "=<" with ">=", and "<="
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Thanks - I figured that out (finally)
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:849DC6A0-15EF-4616-BC47-555F941C4199@.microsoft.com...
> Well for one thing You have "=>" In there, and that's wrong, it should
be
> ">=".
> Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
> month... depending on server settings... A format that always works is
> CCYYMMDD, or, for Feb 13, 2005,
> '20050213'
> try changing the string literals
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> to
> SELECT @.StartDate = '20050205'
> SELECT @.EndDate = '20050213'
> and see if it works then...
>
> "Wayne Wengert" wrote:
>
wrong?
WHERE
=<|||Thanks - that was what I forgot!
Wayne
"MGFoster" <me@.privacy.com> wrote in message
news:6tpZd.10908$cN6.9661@.newsread1.news.pas.earthlink.net...
> Wayne Wengert wrote:
wrong?
WHERE
=<
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
> function. What you have to do, since you're putting the date values in
> a string, is convert them to string data types. In my example I used
> CHAR(8) to just get a date like this '20040314'.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
> Fnny1XBrRp8n15Q7xe4Mm6VR
> =GT97
> --END PGP SIGNATURE--|||
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
This still could lead to an error if his settings are, say, UK English, and
he says
SET @.startDate = '2/16/2005'
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
Neither do you have to do a CONVERT at all in this case (if he uses YYYYMMDD
in his SET/SELECT then it's already in 112 format), and nor do you have to
surround the date value with strings like you did. This will be sufficient:
WHERE ShowDate >= @.startDate
Finally, more for Wayne than the others, be careful how you define the end
date. If you say <= <somedate_notime> you will include rows with a value of
midnight on that day, but not 12:01 AM or 3:45 PM. If you only have
midnight timestamps in the data then it's no big deal, but if you don't
constrain the data, you're better off using < (@.endDate + 1).
A
Monday, March 19, 2012
Datetime function slowdowns
The function below is a bottleneck and I wonder if there is anything that can
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0--Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then--Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
Regards,
Jamie
Jamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0--Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then--Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
Regards,
Jamie
Jamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
Datetime function slowdowns
The function below is a bottleneck and I wonder if there is anything that ca
n
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that
can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate)
- 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
n
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that
can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate)
- 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
Datetime function slowdowns
The function below is a bottleneck and I wonder if there is anything that can
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference = case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference => case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference = case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference => case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie
Thursday, March 8, 2012
DATETIME conversion problem in stored procedure
Hi,
I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears 
Stored procedure:
ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1
VB.NET code:
Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))
When I run this the ReturnVal is 0.
I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.
hi,
dazfl wrote:
Hi,
I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears
Stored procedure:
ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1
usually return values other than 0 (zero) indicate a procedure error.. so, 1 is usually read as error and not "success"..
VB.NET code:
Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))When I run this the ReturnVal is 0.
I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.
try directly consuming a command and relative parameters, like
Dim cmd As New SqlClient.SqlCommandWith cmd
.CommandText = "schema.procedureName"
.CommandType = CommandType.StoredProcedure
.CommandTimeout = n
.Connection = connection
Dim p As New SqlClient.SqlParameter
With p
.ParameterName = "@.testvalue"
.SqlDbType = SqlDbType.DateTime
.Value = DateTime.Now
.Direction = ParameterDirection.Input
End With
.Parameters.Add(p)
End With
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
so that you can check (1st important addition of the command and parameters behaviour) and validate parameters initialization... more.. the parameter automatically handles this kind of conversions..
regards
Wednesday, March 7, 2012
DateTime Bugs?
Hi,
Below shown simple script to get the w
day. Any idea why the wday
for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w to be considered
as first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of ws-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was not
popular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w on Sunday long before ISO. Besides, you can change the
start day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of ws-within-year numbers.
||||Hello, Joe
Indeed, the w numbers returned by the DATEPART are not the ISO w
numbers. There is an example in Books Online on how to create a UDF to
return the ISO w number. However, the original poster was talking
about wdays, not w numbers (which is a completely different
thing).
Razvan|||As indicated in other posts, day of w is dependent on which country you l
ive in. In the US,
Sunday is the first day of the w. In Sweden (and majority of Europe, prob
ably all), first day of
w is Monday. DATEPART to calculate day of w is dependent on SET LANGUA
GE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>
Below shown simple script to get the w
day. Any idea why the wdayfor spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w
to be consideredas first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the w
day. Any idea why the wday> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of w
s-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was notpopular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w
on Sunday long before ISO. Besides, you can change thestart day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of w
s-within-year numbers.||||Hello, Joe
Indeed, the w
numbers returned by the DATEPART are not the ISO wnumbers. There is an example in Books Online on how to create a UDF to
return the ISO w
number. However, the original poster was talkingabout w
days, not w numbers (which is a completely differentthing).
Razvan|||As indicated in other posts, day of w
is dependent on which country you live in. In the US,
Sunday is the first day of the w
. In Sweden (and majority of Europe, probably all), first day of
w
is Monday. DATEPART to calculate day of w is dependent on SET LANGUAGE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the w
day. Any idea why the wday for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>
Datetime and conversion to smalldatetime.
I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.
I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?
Thanks
mes
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"
DateTimes aren't "formatted". It sounds like you are giving it a string, that you think looks like a date. Give the database a real datetime (Or specify the sqlparameter as being of datetime type), and your problem should go away. If that string format isn't valid for your culture, well... You'll have to fix that separately, or manually convert your "DateTime in a string" to a format that your SQL Server wants.DateTime ?
This is my table structure
Date(m/dd/yyyy)
9/09/2006
I want to select month and year in the below format .
Sep 2006 .
How to do that ?
Try this..
SELECT CONVERT(CHAR(6),GETDATE(),109)
|||Sorry Raghu,
Try this..
SELECT LEFT(CONVERT(CHAR(11),GETDATE(),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),GETDATE(),109),4)
|||
Or:
SELECT LEFT(DATENAME(month,'9/09/2006'),3) + ' ' + CONVERT(CHAR(4),Year('9/09/2006')) as DateYouwant
DATETIME
Below SQL works
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select top 10 * from mytable where mydate between @.startdate and @.enddate
But , below one error out with message
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from mytable where mydate between ' + @.startdate +
' AND ' + @.enddate
execute @.sql
Here @.startdate and @.enddate are parameters and used inside a SP.
I have to use dynamic SQL for my logic and don't want to use CONVERT
function.
How to make this dynamic SQL work '
Thx
ShShamin,
the following code should work:
declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
select @.startdate = '06/01/2003'
select @.enddate = '06/03/2003'
select @.sql = 'select * from MyTable where MyTime between '''
+ cast(@.startdate as varchar) + ''' AND ''' + cast (@.enddate as varchar) +
''''
execute (@.sql)
hope this helps
Quentin
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#TgE1pwSDHA.2196@.TK2MSFTNGP12.phx.gbl...
> Below SQL works
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select top 10 * from mytable where mydate between @.startdate and
@.enddate
> But , below one error out with message
> Server: Msg 241, Level 16, State 1, Line 4
> Syntax error converting datetime from character string.
> declare @.startdate datetime, @.enddate datetime, @.sql varchar(1000)
> select @.startdate = '06/01/2003'
> select @.enddate = '06/03/2003'
> select @.sql = 'select * from mytable where mydate between ' + @.startdate
+
> ' AND ' + @.enddate
> execute @.sql
> Here @.startdate and @.enddate are parameters and used inside a SP.
> I have to use dynamic SQL for my logic and don't want to use CONVERT
> function.
> How to make this dynamic SQL work '
> Thx
> Sh
>
>
Saturday, February 25, 2012
Dates problem in SQL Server Evrywhere edition.
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
-- -- -- --
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
Replied in another thread (same question)|||Replied in another thread. (for same question)
Dates problem in SQL Server Everywhere edition.
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
-- -- -- --
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.
Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.
Please use quote for inserting datetime value, it will solve the problem.
use like
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,'4/7/2006',DEFAULT)
Thanks
Sachin
|||
Thank you,
The information was very good,
the problem with the dates is now solved.
Toni.
Dates problem in SQL Server Everywhere edition.
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
-- -- -- --
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
Repliedin another thread (same question)
Dates & Stored Procedure
Hi
I want to know how can i Pass tow Dates to Stored Procedure in sql server
2000 by using vb6.
here below my code i used northwind database ,when i run the code i got
error in the cmd.excute .
i thing my problem in passing date, can any when tell me what is the problem
Private Sub Command2_Click()
Call connect
Call setup
Dim cmd As New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Employee Sales by Country"
Dim parm As New ADODB.Parameter
Dim parm2 As New ADODB.Parameter
Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/1997")
Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/2005")
cmd.Parameters.Append parm
cmd.Parameters.Append parm2
Dim RST As New ADODB.Recordset
Set RST = cmd.Execute
Set DataGrid1.DataSource = RST
DataGrid1.ReBind
End SubWhat does "got error" mean? Could you tell us the exact error message, and
what line it corresponds to here?
On 3/20/05 11:55 PM, in article
D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com, "ayman"
<ayman@.discussions.microsoft.com> wrote:
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the probl
em
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>|||Ayman,
The parameters for the sample procedure [Employee Sales by Country] are
named @.Beginning_Date and @.Ending_Date. You have named both of
your parameters ShippedDate. Could that be the problem?
Steve Kass
Drew University
ayman wrote:
>Hi
>I want to know how can i Pass tow Dates to Stored Procedure in sql server
>2000 by using vb6.
>here below my code i used northwind database ,when i run the code i got
>error in the cmd.excute .
>i thing my problem in passing date, can any when tell me what is the proble
m
>Private Sub Command2_Click()
>Call connect
>Call setup
>
>Dim cmd As New ADODB.Command
>cmd.ActiveConnection = Con
>cmd.CommandType = adCmdStoredProc
>cmd.CommandText = "Employee Sales by Country"
>Dim parm As New ADODB.Parameter
>Dim parm2 As New ADODB.Parameter
>Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/1997")
>Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/2005")
>cmd.Parameters.Append parm
>cmd.Parameters.Append parm2
>
>Dim RST As New ADODB.Recordset
>Set RST = cmd.Execute
>Set DataGrid1.DataSource = RST
>DataGrid1.ReBind
>End Sub
>
>
>|||ayman
Have you tried to format the dates as 'YYYYMMDD'?
"ayman" <ayman@.discussions.microsoft.com> wrote in message
news:D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com...
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the
problem
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>
I want to know how can i Pass tow Dates to Stored Procedure in sql server
2000 by using vb6.
here below my code i used northwind database ,when i run the code i got
error in the cmd.excute .
i thing my problem in passing date, can any when tell me what is the problem
Private Sub Command2_Click()
Call connect
Call setup
Dim cmd As New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Employee Sales by Country"
Dim parm As New ADODB.Parameter
Dim parm2 As New ADODB.Parameter
Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/1997")
Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/2005")
cmd.Parameters.Append parm
cmd.Parameters.Append parm2
Dim RST As New ADODB.Recordset
Set RST = cmd.Execute
Set DataGrid1.DataSource = RST
DataGrid1.ReBind
End SubWhat does "got error" mean? Could you tell us the exact error message, and
what line it corresponds to here?
On 3/20/05 11:55 PM, in article
D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com, "ayman"
<ayman@.discussions.microsoft.com> wrote:
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the probl
em
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>|||Ayman,
The parameters for the sample procedure [Employee Sales by Country] are
named @.Beginning_Date and @.Ending_Date. You have named both of
your parameters ShippedDate. Could that be the problem?
Steve Kass
Drew University
ayman wrote:
>Hi
>I want to know how can i Pass tow Dates to Stored Procedure in sql server
>2000 by using vb6.
>here below my code i used northwind database ,when i run the code i got
>error in the cmd.excute .
>i thing my problem in passing date, can any when tell me what is the proble
m
>Private Sub Command2_Click()
>Call connect
>Call setup
>
>Dim cmd As New ADODB.Command
>cmd.ActiveConnection = Con
>cmd.CommandType = adCmdStoredProc
>cmd.CommandText = "Employee Sales by Country"
>Dim parm As New ADODB.Parameter
>Dim parm2 As New ADODB.Parameter
>Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/1997")
>Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/2005")
>cmd.Parameters.Append parm
>cmd.Parameters.Append parm2
>
>Dim RST As New ADODB.Recordset
>Set RST = cmd.Execute
>Set DataGrid1.DataSource = RST
>DataGrid1.ReBind
>End Sub
>
>
>|||ayman
Have you tried to format the dates as 'YYYYMMDD'?
"ayman" <ayman@.discussions.microsoft.com> wrote in message
news:D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com...
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the
problem
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>
Friday, February 24, 2012
DATEPART and DATEDIFF using VARCHAR(24) Date Format
My counterdatetime field format is varchar(24) listed below.
This format cannot be changed because it's output from perfmon. How can I
change the sql query to recognize DATEPART and DATEDIFF with my
counterdatetime field in varchar(24) format.
Please help me resolve the problem.
Thank You,
select a.counterdatetime, t.countername, avg (a.countervalue)
from counterdata a (NOLOCK),
counterdetails t (NOLOCK)
where a.counterdatetime > '2005-01-20'
AND a.CounterID = t.CounterID
AND t.countername like 'Data File(s) Size (KB)'
AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
group by a.counterdatetime, t.countername
order by a.counterdatetime
Error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
a.counterdatetime
2005-01-20 00:00:35.316
2005-01-20 00:01:35.316Joe,
You should have stored the data in the table as a datetime datatype iand not
a character. In any case try setting the dateformat and see if that helps:
SET DATEFORMAT YMD
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:55ABEE1A-2D7B-470E-9D93-AFDE09F135A6@.microsoft.com...
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>|||try this
convert(datetime,@.counterdatetime, 101)
Thanks,
RK
"Joe K." wrote:
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>
This format cannot be changed because it's output from perfmon. How can I
change the sql query to recognize DATEPART and DATEDIFF with my
counterdatetime field in varchar(24) format.
Please help me resolve the problem.
Thank You,
select a.counterdatetime, t.countername, avg (a.countervalue)
from counterdata a (NOLOCK),
counterdetails t (NOLOCK)
where a.counterdatetime > '2005-01-20'
AND a.CounterID = t.CounterID
AND t.countername like 'Data File(s) Size (KB)'
AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
AND DATEPART(w
day,a.counterdatetime) BETWEEN 2 AND 6group by a.counterdatetime, t.countername
order by a.counterdatetime
Error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
a.counterdatetime
2005-01-20 00:00:35.316
2005-01-20 00:01:35.316Joe,
You should have stored the data in the table as a datetime datatype iand not
a character. In any case try setting the dateformat and see if that helps:
SET DATEFORMAT YMD
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:55ABEE1A-2D7B-470E-9D93-AFDE09F135A6@.microsoft.com...
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(w
day,a.counterdatetime) BETWEEN 2 AND 6> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>|||try this
convert(datetime,@.counterdatetime, 101)
Thanks,
RK
"Joe K." wrote:
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(w
day,a.counterdatetime) BETWEEN 2 AND 6> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>
Sunday, February 19, 2012
datediff() alters other values in nested iif
i have a nested IIF statement, see below, that evaluates all possible field
values of a particular field, and outputs appropriate text. The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"
I convert the last possible value type to date by concatenation:
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
The report works fine like this , but once I introduce the DATEDIFF(),
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
generating a invalid date function. In other words, instead of the values
"FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
suitable values that can be converted to a date format, they too are
subjected to CDATE(). You can clearly see this in the error below as "BASIC"
is converted to "01/IC/BA"
complete IIF expression:
=IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))= "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,"OLD","FASHION")))
error generated when DATEDIFF() is introduced:
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textbox
â'textbox25â' contains an error: Conversion from string "01/IC/BA" to type
'Date' is not valid.
thanks for you help in advance.
anthonyinstead of using cdate, do you think datevalue may work better?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>i have a nested IIF statement, see below, that evaluates all possible field
> values of a particular field, and outputs appropriate text. The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"
> I convert the last possible value type to date by concatenation:
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> The report works fine like this , but once I introduce the DATEDIFF(),
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> generating a invalid date function. In other words, instead of the values
> "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> suitable values that can be converted to a date format, they too are
> subjected to CDATE(). You can clearly see this in the error below as
> "BASIC"
> is converted to "01/IC/BA"
>
> complete IIF expression:
> =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,"OLD","FASHION")))
>
> error generated when DATEDIFF() is introduced:
> Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> textbox
> 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> 'Date' is not valid.
> thanks for you help in advance.
> anthony
>
>|||thanks for the quick reply...cdate is not the issue it's the introduction of
datediff that appears to be called prior to the earlier conditions in the
nested iif being evaluated. as a result, the date expression is evaluated on
data that should have been accounted for before...see the error message
posted "01/IC/BA" will never be recognized as a date no matter what function
i call.
"Ben Watts" wrote:
> instead of using cdate, do you think datevalue may work better?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >i have a nested IIF statement, see below, that evaluates all possible field
> > values of a particular field, and outputs appropriate text. The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"
> >
> > I convert the last possible value type to date by concatenation:
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > The report works fine like this , but once I introduce the DATEDIFF(),
> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> > generating a invalid date function. In other words, instead of the values
> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> > suitable values that can be converted to a date format, they too are
> > subjected to CDATE(). You can clearly see this in the error below as
> > "BASIC"
> > is converted to "01/IC/BA"
> >
> >
> > complete IIF expression:
> >
> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> > < -2,"OLD","FASHION")))
> >
> >
> > error generated when DATEDIFF() is introduced:
> >
> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> > textbox
> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> > 'Date' is not valid.
> >
> > thanks for you help in advance.
> > anthony
> >
> >
> >
> >
>
>|||What are the values of styleseason, that you are trying to convert? So far
I know there is flash and basic, but what are the others?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> thanks for the quick reply...cdate is not the issue it's the introduction
> of
> datediff that appears to be called prior to the earlier conditions in the
> nested iif being evaluated. as a result, the date expression is evaluated
> on
> data that should have been accounted for before...see the error message
> posted "01/IC/BA" will never be recognized as a date no matter what
> function
> i call.
> "Ben Watts" wrote:
>> instead of using cdate, do you think datevalue may work better?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >i have a nested IIF statement, see below, that evaluates all possible
>> >field
>> > values of a particular field, and outputs appropriate text. The
>> > possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"
>> >
>> > I convert the last possible value type to date by concatenation:
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > The report works fine like this , but once I introduce the DATEDIFF(),
>> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> > generating a invalid date function. In other words, instead of the
>> > values
>> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> > only
>> > suitable values that can be converted to a date format, they too are
>> > subjected to CDATE(). You can clearly see this in the error below as
>> > "BASIC"
>> > is converted to "01/IC/BA"
>> >
>> >
>> > complete IIF expression:
>> >
>> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> > < -2,"OLD","FASHION")))
>> >
>> >
>> > error generated when DATEDIFF() is introduced:
>> >
>> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> > textbox
>> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> > type
>> > 'Date' is not valid.
>> >
>> > thanks for you help in advance.
>> > anthony
>> >
>> >
>> >
>> >
>>|||from my original post:
-- The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"--
I am not trying to convert all values only the ones that are not flash or
basic. The other values,which are in yymm format I am doing some string
manipulation and concatenation to get it into a mm/dd/yy format.
"01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
According to my IIF statement I am evaluating conditions for flash and basic
first wich should leave only yymm to convert to mm/dd/yy values. At this
point the report works as it should
BASIC to BASIC
FLASH to OLD
yymm to mm/dd/yy
Once I introduce any date function cdate,dateval or datediff into a sinlge
IIF in the nested IIF statements all styleseason values are evaluated by the
date function and obviously gives an error for the flash and basic values. I
end up with values like
"01/IC/BA" trying to be evaluated, which is BASIC run through the
concatenation. Please see full IIF statement.
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
"Ben Watts" wrote:
> What are the values of styleseason, that you are trying to convert? So far
> I know there is flash and basic, but what are the others?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> > thanks for the quick reply...cdate is not the issue it's the introduction
> > of
> > datediff that appears to be called prior to the earlier conditions in the
> > nested iif being evaluated. as a result, the date expression is evaluated
> > on
> > data that should have been accounted for before...see the error message
> > posted "01/IC/BA" will never be recognized as a date no matter what
> > function
> > i call.
> >
> > "Ben Watts" wrote:
> >
> >> instead of using cdate, do you think datevalue may work better?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >field
> >> > values of a particular field, and outputs appropriate text. The
> >> > possible
> >> > field values for Fields!STYLESEASON, are
> >> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> >> > e.g.
> >> > "0604"
> >> >
> >> > I convert the last possible value type to date by concatenation:
> >> >
> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > The report works fine like this , but once I introduce the DATEDIFF(),
> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> > generating a invalid date function. In other words, instead of the
> >> > values
> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> > only
> >> > suitable values that can be converted to a date format, they too are
> >> > subjected to CDATE(). You can clearly see this in the error below as
> >> > "BASIC"
> >> > is converted to "01/IC/BA"
> >> >
> >> >
> >> > complete IIF expression:
> >> >
> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> > < -2,"OLD","FASHION")))
> >> >
> >> >
> >> > error generated when DATEDIFF() is introduced:
> >> >
> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> > textbox
> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> > type
> >> > 'Date' is not valid.
> >> >
> >> > thanks for you help in advance.
> >> > anthony
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||I see a wayof trying it. You could either write some sort of CASE statment
in your select statement setting the value to a field. Like:
CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
Then write your nested if, something like this.
iif(Fields!Date.Value = 'neither' and
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,
'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
I think you see where I am taking this. Basically the case statment will be
handled first then the rest of it will also be handled in turn. I hope this
helps.
Then enter that instead of your nested if and that should work.
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> from my original post:
> -- The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"--
> I am not trying to convert all values only the ones that are not flash or
> basic. The other values,which are in yymm format I am doing some string
> manipulation and concatenation to get it into a mm/dd/yy format.
> "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> According to my IIF statement I am evaluating conditions for flash and
> basic
> first wich should leave only yymm to convert to mm/dd/yy values. At this
> point the report works as it should
> BASIC to BASIC
> FLASH to OLD
> yymm to mm/dd/yy
> Once I introduce any date function cdate,dateval or datediff into a
> sinlge
> IIF in the nested IIF statements all styleseason values are evaluated by
> the
> date function and obviously gives an error for the flash and basic values.
> I
> end up with values like
> "01/IC/BA" trying to be evaluated, which is BASIC run through the
> concatenation. Please see full IIF statement.
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>
>
>
> "Ben Watts" wrote:
>> What are the values of styleseason, that you are trying to convert? So
>> far
>> I know there is flash and basic, but what are the others?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> > thanks for the quick reply...cdate is not the issue it's the
>> > introduction
>> > of
>> > datediff that appears to be called prior to the earlier conditions in
>> > the
>> > nested iif being evaluated. as a result, the date expression is
>> > evaluated
>> > on
>> > data that should have been accounted for before...see the error message
>> > posted "01/IC/BA" will never be recognized as a date no matter what
>> > function
>> > i call.
>> >
>> > "Ben Watts" wrote:
>> >
>> >> instead of using cdate, do you think datevalue may work better?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >i have a nested IIF statement, see below, that evaluates all possible
>> >> >field
>> >> > values of a particular field, and outputs appropriate text. The
>> >> > possible
>> >> > field values for Fields!STYLESEASON, are
>> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> > format.
>> >> > e.g.
>> >> > "0604"
>> >> >
>> >> > I convert the last possible value type to date by concatenation:
>> >> >
>> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > The report works fine like this , but once I introduce the
>> >> > DATEDIFF(),
>> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> >> > generating a invalid date function. In other words, instead of the
>> >> > values
>> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> >> > only
>> >> > suitable values that can be converted to a date format, they too are
>> >> > subjected to CDATE(). You can clearly see this in the error below
>> >> > as
>> >> > "BASIC"
>> >> > is converted to "01/IC/BA"
>> >> >
>> >> >
>> >> > complete IIF expression:
>> >> >
>> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> > < -2,"OLD","FASHION")))
>> >> >
>> >> >
>> >> > error generated when DATEDIFF() is introduced:
>> >> >
>> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> >> > textbox
>> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> >> > type
>> >> > 'Date' is not valid.
>> >> >
>> >> > thanks for you help in advance.
>> >> > anthony
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||i was actually trying to avoid doing any of the cases in sql, but it looks
like ill have to do it that way. don't you think it is a bug of some sort as
to why calling the function in the iif takes precedence over the previous
conditional statements. in any regards, thank you for your time and help.
"Ben Watts" wrote:
> I see a wayof trying it. You could either write some sort of CASE statment
> in your select statement setting the value to a field. Like:
> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
> Then write your nested if, something like this.
> iif(Fields!Date.Value = 'neither' and
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,
> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
> I think you see where I am taking this. Basically the case statment will be
> handled first then the rest of it will also be handled in turn. I hope this
> helps.
>
> Then enter that instead of your nested if and that should work.
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> > from my original post:
> > -- The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"--
> >
> > I am not trying to convert all values only the ones that are not flash or
> > basic. The other values,which are in yymm format I am doing some string
> > manipulation and concatenation to get it into a mm/dd/yy format.
> >
> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> >
> > According to my IIF statement I am evaluating conditions for flash and
> > basic
> > first wich should leave only yymm to convert to mm/dd/yy values. At this
> > point the report works as it should
> >
> > BASIC to BASIC
> > FLASH to OLD
> > yymm to mm/dd/yy
> >
> > Once I introduce any date function cdate,dateval or datediff into a
> > sinlge
> > IIF in the nested IIF statements all styleseason values are evaluated by
> > the
> > date function and obviously gives an error for the flash and basic values.
> > I
> > end up with values like
> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
> > concatenation. Please see full IIF statement.
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> >
> >
> >
> >
> >
> > "Ben Watts" wrote:
> >
> >> What are the values of styleseason, that you are trying to convert? So
> >> far
> >> I know there is flash and basic, but what are the others?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> >> > thanks for the quick reply...cdate is not the issue it's the
> >> > introduction
> >> > of
> >> > datediff that appears to be called prior to the earlier conditions in
> >> > the
> >> > nested iif being evaluated. as a result, the date expression is
> >> > evaluated
> >> > on
> >> > data that should have been accounted for before...see the error message
> >> > posted "01/IC/BA" will never be recognized as a date no matter what
> >> > function
> >> > i call.
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> instead of using cdate, do you think datevalue may work better?
> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >> >field
> >> >> > values of a particular field, and outputs appropriate text. The
> >> >> > possible
> >> >> > field values for Fields!STYLESEASON, are
> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
> >> >> > format.
> >> >> > e.g.
> >> >> > "0604"
> >> >> >
> >> >> > I convert the last possible value type to date by concatenation:
> >> >> >
> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > The report works fine like this , but once I introduce the
> >> >> > DATEDIFF(),
> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> >> > generating a invalid date function. In other words, instead of the
> >> >> > values
> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> >> > only
> >> >> > suitable values that can be converted to a date format, they too are
> >> >> > subjected to CDATE(). You can clearly see this in the error below
> >> >> > as
> >> >> > "BASIC"
> >> >> > is converted to "01/IC/BA"
> >> >> >
> >> >> >
> >> >> > complete IIF expression:
> >> >> >
> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> > < -2,"OLD","FASHION")))
> >> >> >
> >> >> >
> >> >> > error generated when DATEDIFF() is introduced:
> >> >> >
> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> >> > textbox
> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> >> > type
> >> >> > 'Date' is not valid.
> >> >> >
> >> >> > thanks for you help in advance.
> >> >> > anthony
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It really does seem like a bug, you could always put that portion of the if
statement first and see if it works that way. I have had weirder things
happen with if statements. Good luck
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:A9BF9E34-15F4-43C5-980E-A6AF59605C16@.microsoft.com...
>i was actually trying to avoid doing any of the cases in sql, but it looks
> like ill have to do it that way. don't you think it is a bug of some sort
> as
> to why calling the function in the iif takes precedence over the previous
> conditional statements. in any regards, thank you for your time and help.
> "Ben Watts" wrote:
>> I see a wayof trying it. You could either write some sort of CASE
>> statment
>> in your select statement setting the value to a field. Like:
>> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
>> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as
>> Date
>> Then write your nested if, something like this.
>> iif(Fields!Date.Value = 'neither' and
>> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> < -2,
>> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
>> I think you see where I am taking this. Basically the case statment will
>> be
>> handled first then the rest of it will also be handled in turn. I hope
>> this
>> helps.
>>
>> Then enter that instead of your nested if and that should work.
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
>> > from my original post:
>> > -- The possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"--
>> >
>> > I am not trying to convert all values only the ones that are not flash
>> > or
>> > basic. The other values,which are in yymm format I am doing some
>> > string
>> > manipulation and concatenation to get it into a mm/dd/yy format.
>> >
>> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
>> >
>> > According to my IIF statement I am evaluating conditions for flash and
>> > basic
>> > first wich should leave only yymm to convert to mm/dd/yy values. At
>> > this
>> > point the report works as it should
>> >
>> > BASIC to BASIC
>> > FLASH to OLD
>> > yymm to mm/dd/yy
>> >
>> > Once I introduce any date function cdate,dateval or datediff into a
>> > sinlge
>> > IIF in the nested IIF statements all styleseason values are evaluated
>> > by
>> > the
>> > date function and obviously gives an error for the flash and basic
>> > values.
>> > I
>> > end up with values like
>> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
>> > concatenation. Please see full IIF statement.
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Ben Watts" wrote:
>> >
>> >> What are the values of styleseason, that you are trying to convert?
>> >> So
>> >> far
>> >> I know there is flash and basic, but what are the others?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> >> > thanks for the quick reply...cdate is not the issue it's the
>> >> > introduction
>> >> > of
>> >> > datediff that appears to be called prior to the earlier conditions
>> >> > in
>> >> > the
>> >> > nested iif being evaluated. as a result, the date expression is
>> >> > evaluated
>> >> > on
>> >> > data that should have been accounted for before...see the error
>> >> > message
>> >> > posted "01/IC/BA" will never be recognized as a date no matter what
>> >> > function
>> >> > i call.
>> >> >
>> >> > "Ben Watts" wrote:
>> >> >
>> >> >> instead of using cdate, do you think datevalue may work better?
>> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >> >i have a nested IIF statement, see below, that evaluates all
>> >> >> >possible
>> >> >> >field
>> >> >> > values of a particular field, and outputs appropriate text. The
>> >> >> > possible
>> >> >> > field values for Fields!STYLESEASON, are
>> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> >> > format.
>> >> >> > e.g.
>> >> >> > "0604"
>> >> >> >
>> >> >> > I convert the last possible value type to date by concatenation:
>> >> >> >
>> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > The report works fine like this , but once I introduce the
>> >> >> > DATEDIFF(),
>> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by
>> >> >> > CDATE()
>> >> >> > generating a invalid date function. In other words, instead of
>> >> >> > the
>> >> >> > values
>> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions
>> >> >> > leaving
>> >> >> > only
>> >> >> > suitable values that can be converted to a date format, they too
>> >> >> > are
>> >> >> > subjected to CDATE(). You can clearly see this in the error
>> >> >> > below
>> >> >> > as
>> >> >> > "BASIC"
>> >> >> > is converted to "01/IC/BA"
>> >> >> >
>> >> >> >
>> >> >> > complete IIF expression:
>> >> >> >
>> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> > < -2,"OLD","FASHION")))
>> >> >> >
>> >> >> >
>> >> >> > error generated when DATEDIFF() is introduced:
>> >> >> >
>> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for
>> >> >> > the
>> >> >> > textbox
>> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA"
>> >> >> > to
>> >> >> > type
>> >> >> > 'Date' is not valid.
>> >> >> >
>> >> >> > thanks for you help in advance.
>> >> >> > anthony
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
values of a particular field, and outputs appropriate text. The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"
I convert the last possible value type to date by concatenation:
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
The report works fine like this , but once I introduce the DATEDIFF(),
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
generating a invalid date function. In other words, instead of the values
"FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
suitable values that can be converted to a date format, they too are
subjected to CDATE(). You can clearly see this in the error below as "BASIC"
is converted to "01/IC/BA"
complete IIF expression:
=IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))= "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,"OLD","FASHION")))
error generated when DATEDIFF() is introduced:
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textbox
â'textbox25â' contains an error: Conversion from string "01/IC/BA" to type
'Date' is not valid.
thanks for you help in advance.
anthonyinstead of using cdate, do you think datevalue may work better?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>i have a nested IIF statement, see below, that evaluates all possible field
> values of a particular field, and outputs appropriate text. The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"
> I convert the last possible value type to date by concatenation:
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> The report works fine like this , but once I introduce the DATEDIFF(),
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> generating a invalid date function. In other words, instead of the values
> "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> suitable values that can be converted to a date format, they too are
> subjected to CDATE(). You can clearly see this in the error below as
> "BASIC"
> is converted to "01/IC/BA"
>
> complete IIF expression:
> =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,"OLD","FASHION")))
>
> error generated when DATEDIFF() is introduced:
> Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> textbox
> 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> 'Date' is not valid.
> thanks for you help in advance.
> anthony
>
>|||thanks for the quick reply...cdate is not the issue it's the introduction of
datediff that appears to be called prior to the earlier conditions in the
nested iif being evaluated. as a result, the date expression is evaluated on
data that should have been accounted for before...see the error message
posted "01/IC/BA" will never be recognized as a date no matter what function
i call.
"Ben Watts" wrote:
> instead of using cdate, do you think datevalue may work better?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >i have a nested IIF statement, see below, that evaluates all possible field
> > values of a particular field, and outputs appropriate text. The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"
> >
> > I convert the last possible value type to date by concatenation:
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > The report works fine like this , but once I introduce the DATEDIFF(),
> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> > generating a invalid date function. In other words, instead of the values
> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> > suitable values that can be converted to a date format, they too are
> > subjected to CDATE(). You can clearly see this in the error below as
> > "BASIC"
> > is converted to "01/IC/BA"
> >
> >
> > complete IIF expression:
> >
> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> > < -2,"OLD","FASHION")))
> >
> >
> > error generated when DATEDIFF() is introduced:
> >
> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> > textbox
> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> > 'Date' is not valid.
> >
> > thanks for you help in advance.
> > anthony
> >
> >
> >
> >
>
>|||What are the values of styleseason, that you are trying to convert? So far
I know there is flash and basic, but what are the others?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> thanks for the quick reply...cdate is not the issue it's the introduction
> of
> datediff that appears to be called prior to the earlier conditions in the
> nested iif being evaluated. as a result, the date expression is evaluated
> on
> data that should have been accounted for before...see the error message
> posted "01/IC/BA" will never be recognized as a date no matter what
> function
> i call.
> "Ben Watts" wrote:
>> instead of using cdate, do you think datevalue may work better?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >i have a nested IIF statement, see below, that evaluates all possible
>> >field
>> > values of a particular field, and outputs appropriate text. The
>> > possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"
>> >
>> > I convert the last possible value type to date by concatenation:
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > The report works fine like this , but once I introduce the DATEDIFF(),
>> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> > generating a invalid date function. In other words, instead of the
>> > values
>> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> > only
>> > suitable values that can be converted to a date format, they too are
>> > subjected to CDATE(). You can clearly see this in the error below as
>> > "BASIC"
>> > is converted to "01/IC/BA"
>> >
>> >
>> > complete IIF expression:
>> >
>> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> > < -2,"OLD","FASHION")))
>> >
>> >
>> > error generated when DATEDIFF() is introduced:
>> >
>> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> > textbox
>> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> > type
>> > 'Date' is not valid.
>> >
>> > thanks for you help in advance.
>> > anthony
>> >
>> >
>> >
>> >
>>|||from my original post:
-- The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"--
I am not trying to convert all values only the ones that are not flash or
basic. The other values,which are in yymm format I am doing some string
manipulation and concatenation to get it into a mm/dd/yy format.
"01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
According to my IIF statement I am evaluating conditions for flash and basic
first wich should leave only yymm to convert to mm/dd/yy values. At this
point the report works as it should
BASIC to BASIC
FLASH to OLD
yymm to mm/dd/yy
Once I introduce any date function cdate,dateval or datediff into a sinlge
IIF in the nested IIF statements all styleseason values are evaluated by the
date function and obviously gives an error for the flash and basic values. I
end up with values like
"01/IC/BA" trying to be evaluated, which is BASIC run through the
concatenation. Please see full IIF statement.
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
"Ben Watts" wrote:
> What are the values of styleseason, that you are trying to convert? So far
> I know there is flash and basic, but what are the others?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> > thanks for the quick reply...cdate is not the issue it's the introduction
> > of
> > datediff that appears to be called prior to the earlier conditions in the
> > nested iif being evaluated. as a result, the date expression is evaluated
> > on
> > data that should have been accounted for before...see the error message
> > posted "01/IC/BA" will never be recognized as a date no matter what
> > function
> > i call.
> >
> > "Ben Watts" wrote:
> >
> >> instead of using cdate, do you think datevalue may work better?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >field
> >> > values of a particular field, and outputs appropriate text. The
> >> > possible
> >> > field values for Fields!STYLESEASON, are
> >> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> >> > e.g.
> >> > "0604"
> >> >
> >> > I convert the last possible value type to date by concatenation:
> >> >
> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > The report works fine like this , but once I introduce the DATEDIFF(),
> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> > generating a invalid date function. In other words, instead of the
> >> > values
> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> > only
> >> > suitable values that can be converted to a date format, they too are
> >> > subjected to CDATE(). You can clearly see this in the error below as
> >> > "BASIC"
> >> > is converted to "01/IC/BA"
> >> >
> >> >
> >> > complete IIF expression:
> >> >
> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> > < -2,"OLD","FASHION")))
> >> >
> >> >
> >> > error generated when DATEDIFF() is introduced:
> >> >
> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> > textbox
> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> > type
> >> > 'Date' is not valid.
> >> >
> >> > thanks for you help in advance.
> >> > anthony
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||I see a wayof trying it. You could either write some sort of CASE statment
in your select statement setting the value to a field. Like:
CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
Then write your nested if, something like this.
iif(Fields!Date.Value = 'neither' and
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,
'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
I think you see where I am taking this. Basically the case statment will be
handled first then the rest of it will also be handled in turn. I hope this
helps.
Then enter that instead of your nested if and that should work.
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> from my original post:
> -- The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"--
> I am not trying to convert all values only the ones that are not flash or
> basic. The other values,which are in yymm format I am doing some string
> manipulation and concatenation to get it into a mm/dd/yy format.
> "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> According to my IIF statement I am evaluating conditions for flash and
> basic
> first wich should leave only yymm to convert to mm/dd/yy values. At this
> point the report works as it should
> BASIC to BASIC
> FLASH to OLD
> yymm to mm/dd/yy
> Once I introduce any date function cdate,dateval or datediff into a
> sinlge
> IIF in the nested IIF statements all styleseason values are evaluated by
> the
> date function and obviously gives an error for the flash and basic values.
> I
> end up with values like
> "01/IC/BA" trying to be evaluated, which is BASIC run through the
> concatenation. Please see full IIF statement.
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>
>
>
> "Ben Watts" wrote:
>> What are the values of styleseason, that you are trying to convert? So
>> far
>> I know there is flash and basic, but what are the others?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> > thanks for the quick reply...cdate is not the issue it's the
>> > introduction
>> > of
>> > datediff that appears to be called prior to the earlier conditions in
>> > the
>> > nested iif being evaluated. as a result, the date expression is
>> > evaluated
>> > on
>> > data that should have been accounted for before...see the error message
>> > posted "01/IC/BA" will never be recognized as a date no matter what
>> > function
>> > i call.
>> >
>> > "Ben Watts" wrote:
>> >
>> >> instead of using cdate, do you think datevalue may work better?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >i have a nested IIF statement, see below, that evaluates all possible
>> >> >field
>> >> > values of a particular field, and outputs appropriate text. The
>> >> > possible
>> >> > field values for Fields!STYLESEASON, are
>> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> > format.
>> >> > e.g.
>> >> > "0604"
>> >> >
>> >> > I convert the last possible value type to date by concatenation:
>> >> >
>> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > The report works fine like this , but once I introduce the
>> >> > DATEDIFF(),
>> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> >> > generating a invalid date function. In other words, instead of the
>> >> > values
>> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> >> > only
>> >> > suitable values that can be converted to a date format, they too are
>> >> > subjected to CDATE(). You can clearly see this in the error below
>> >> > as
>> >> > "BASIC"
>> >> > is converted to "01/IC/BA"
>> >> >
>> >> >
>> >> > complete IIF expression:
>> >> >
>> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> > < -2,"OLD","FASHION")))
>> >> >
>> >> >
>> >> > error generated when DATEDIFF() is introduced:
>> >> >
>> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> >> > textbox
>> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> >> > type
>> >> > 'Date' is not valid.
>> >> >
>> >> > thanks for you help in advance.
>> >> > anthony
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||i was actually trying to avoid doing any of the cases in sql, but it looks
like ill have to do it that way. don't you think it is a bug of some sort as
to why calling the function in the iif takes precedence over the previous
conditional statements. in any regards, thank you for your time and help.
"Ben Watts" wrote:
> I see a wayof trying it. You could either write some sort of CASE statment
> in your select statement setting the value to a field. Like:
> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
> Then write your nested if, something like this.
> iif(Fields!Date.Value = 'neither' and
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,
> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
> I think you see where I am taking this. Basically the case statment will be
> handled first then the rest of it will also be handled in turn. I hope this
> helps.
>
> Then enter that instead of your nested if and that should work.
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> > from my original post:
> > -- The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"--
> >
> > I am not trying to convert all values only the ones that are not flash or
> > basic. The other values,which are in yymm format I am doing some string
> > manipulation and concatenation to get it into a mm/dd/yy format.
> >
> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> >
> > According to my IIF statement I am evaluating conditions for flash and
> > basic
> > first wich should leave only yymm to convert to mm/dd/yy values. At this
> > point the report works as it should
> >
> > BASIC to BASIC
> > FLASH to OLD
> > yymm to mm/dd/yy
> >
> > Once I introduce any date function cdate,dateval or datediff into a
> > sinlge
> > IIF in the nested IIF statements all styleseason values are evaluated by
> > the
> > date function and obviously gives an error for the flash and basic values.
> > I
> > end up with values like
> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
> > concatenation. Please see full IIF statement.
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> >
> >
> >
> >
> >
> > "Ben Watts" wrote:
> >
> >> What are the values of styleseason, that you are trying to convert? So
> >> far
> >> I know there is flash and basic, but what are the others?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> >> > thanks for the quick reply...cdate is not the issue it's the
> >> > introduction
> >> > of
> >> > datediff that appears to be called prior to the earlier conditions in
> >> > the
> >> > nested iif being evaluated. as a result, the date expression is
> >> > evaluated
> >> > on
> >> > data that should have been accounted for before...see the error message
> >> > posted "01/IC/BA" will never be recognized as a date no matter what
> >> > function
> >> > i call.
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> instead of using cdate, do you think datevalue may work better?
> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >> >field
> >> >> > values of a particular field, and outputs appropriate text. The
> >> >> > possible
> >> >> > field values for Fields!STYLESEASON, are
> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
> >> >> > format.
> >> >> > e.g.
> >> >> > "0604"
> >> >> >
> >> >> > I convert the last possible value type to date by concatenation:
> >> >> >
> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > The report works fine like this , but once I introduce the
> >> >> > DATEDIFF(),
> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> >> > generating a invalid date function. In other words, instead of the
> >> >> > values
> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> >> > only
> >> >> > suitable values that can be converted to a date format, they too are
> >> >> > subjected to CDATE(). You can clearly see this in the error below
> >> >> > as
> >> >> > "BASIC"
> >> >> > is converted to "01/IC/BA"
> >> >> >
> >> >> >
> >> >> > complete IIF expression:
> >> >> >
> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> > < -2,"OLD","FASHION")))
> >> >> >
> >> >> >
> >> >> > error generated when DATEDIFF() is introduced:
> >> >> >
> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> >> > textbox
> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> >> > type
> >> >> > 'Date' is not valid.
> >> >> >
> >> >> > thanks for you help in advance.
> >> >> > anthony
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It really does seem like a bug, you could always put that portion of the if
statement first and see if it works that way. I have had weirder things
happen with if statements. Good luck
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:A9BF9E34-15F4-43C5-980E-A6AF59605C16@.microsoft.com...
>i was actually trying to avoid doing any of the cases in sql, but it looks
> like ill have to do it that way. don't you think it is a bug of some sort
> as
> to why calling the function in the iif takes precedence over the previous
> conditional statements. in any regards, thank you for your time and help.
> "Ben Watts" wrote:
>> I see a wayof trying it. You could either write some sort of CASE
>> statment
>> in your select statement setting the value to a field. Like:
>> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
>> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as
>> Date
>> Then write your nested if, something like this.
>> iif(Fields!Date.Value = 'neither' and
>> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> < -2,
>> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
>> I think you see where I am taking this. Basically the case statment will
>> be
>> handled first then the rest of it will also be handled in turn. I hope
>> this
>> helps.
>>
>> Then enter that instead of your nested if and that should work.
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
>> > from my original post:
>> > -- The possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"--
>> >
>> > I am not trying to convert all values only the ones that are not flash
>> > or
>> > basic. The other values,which are in yymm format I am doing some
>> > string
>> > manipulation and concatenation to get it into a mm/dd/yy format.
>> >
>> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
>> >
>> > According to my IIF statement I am evaluating conditions for flash and
>> > basic
>> > first wich should leave only yymm to convert to mm/dd/yy values. At
>> > this
>> > point the report works as it should
>> >
>> > BASIC to BASIC
>> > FLASH to OLD
>> > yymm to mm/dd/yy
>> >
>> > Once I introduce any date function cdate,dateval or datediff into a
>> > sinlge
>> > IIF in the nested IIF statements all styleseason values are evaluated
>> > by
>> > the
>> > date function and obviously gives an error for the flash and basic
>> > values.
>> > I
>> > end up with values like
>> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
>> > concatenation. Please see full IIF statement.
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Ben Watts" wrote:
>> >
>> >> What are the values of styleseason, that you are trying to convert?
>> >> So
>> >> far
>> >> I know there is flash and basic, but what are the others?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> >> > thanks for the quick reply...cdate is not the issue it's the
>> >> > introduction
>> >> > of
>> >> > datediff that appears to be called prior to the earlier conditions
>> >> > in
>> >> > the
>> >> > nested iif being evaluated. as a result, the date expression is
>> >> > evaluated
>> >> > on
>> >> > data that should have been accounted for before...see the error
>> >> > message
>> >> > posted "01/IC/BA" will never be recognized as a date no matter what
>> >> > function
>> >> > i call.
>> >> >
>> >> > "Ben Watts" wrote:
>> >> >
>> >> >> instead of using cdate, do you think datevalue may work better?
>> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >> >i have a nested IIF statement, see below, that evaluates all
>> >> >> >possible
>> >> >> >field
>> >> >> > values of a particular field, and outputs appropriate text. The
>> >> >> > possible
>> >> >> > field values for Fields!STYLESEASON, are
>> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> >> > format.
>> >> >> > e.g.
>> >> >> > "0604"
>> >> >> >
>> >> >> > I convert the last possible value type to date by concatenation:
>> >> >> >
>> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > The report works fine like this , but once I introduce the
>> >> >> > DATEDIFF(),
>> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by
>> >> >> > CDATE()
>> >> >> > generating a invalid date function. In other words, instead of
>> >> >> > the
>> >> >> > values
>> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions
>> >> >> > leaving
>> >> >> > only
>> >> >> > suitable values that can be converted to a date format, they too
>> >> >> > are
>> >> >> > subjected to CDATE(). You can clearly see this in the error
>> >> >> > below
>> >> >> > as
>> >> >> > "BASIC"
>> >> >> > is converted to "01/IC/BA"
>> >> >> >
>> >> >> >
>> >> >> > complete IIF expression:
>> >> >> >
>> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> > < -2,"OLD","FASHION")))
>> >> >> >
>> >> >> >
>> >> >> > error generated when DATEDIFF() is introduced:
>> >> >> >
>> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for
>> >> >> > the
>> >> >> > textbox
>> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA"
>> >> >> > to
>> >> >> > type
>> >> >> > 'Date' is not valid.
>> >> >> >
>> >> >> > thanks for you help in advance.
>> >> >> > anthony
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)