• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9279
  • Last Modified:

Conversion failed when converting date and/or time from character string.

Hi
I wouldn't think this is difficult todo but I haven't found solution. Its to pick a column from table from a set period. I get error in title from stored procedure "Conversion failed when converting date and/or time from character string."

Thanks in advance for help.




ALTER PROCEDURE [dbo].[GetHistorianDataByField]
      @InputColumnName nvarchar(30),
      @StartDate datetime,
      @EndDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      DECLARE @sql nvarchar(max)
      SET @sql = 'SELECT ' + @InputColumnName + ' FROM HistorianData '
                 + ' WHERE [TIMESTAMP] >= ' + CONVERT(datetime, @StartDate,21) 
    EXEC(@sql)
END
 
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[GetHistorianDataByField]
		@InputColumnName = N'[PI353005]',
		@StartDate = N'2008-01-01 00:00:00',
		@EndDate = N'2009-01-01 00:00:00'
 
SELECT	'Return Value' = @return_value
 
GO

Open in new window

0
rwallacej
Asked:
rwallacej
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
     SET @sql = 'SELECT ' + @InputColumnName + ' FROM HistorianData '
                 + ' WHERE [TIMESTAMP] >= ''' + CONVERT(varchar, @StartDate,21) +''''
0
 
pcelbaCommented:
ALTER PROCEDURE [dbo].[GetHistorianDataByField]
      @InputColumnName nvarchar(30),
      @StartDate nvarchar(30),
      @EndDate nvarchar(30)
AS

etc.

     SET @sql = 'SELECT ' + @InputColumnName + ' FROM HistorianData '
                 + ' WHERE [TIMESTAMP] >= CONVERT(datetime, @StartDate,21) '
0
 
ezraaCommented:
I don't think you need to convert the date at all.  Just change the startdate and enddate to varchar instead:
alter PROCEDURE [dbo].[GetHistorianDataByField]
      @InputColumnName nvarchar(30),
      @StartDate varchar(21),
      @EndDate varchar(21)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      DECLARE @sql nvarchar(max)
      SET @sql = 'SELECT ' + @InputColumnName + ' FROM HistorianData '
                 + ' WHERE [TIMESTAMP] >= ''' + cast(@StartDate as varchar(21)) + ''''
     EXEC(@sql)
END
 
 
DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetHistorianDataByField]
                @InputColumnName = N'[PI353005]',
                @StartDate = N'2008-01-01 00:00:00',
                @EndDate = N'2009-01-01 00:00:00'
 
SELECT  'Return Value' = @return_value
 
GO

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
pcelbaCommented:
Another question is TIMESTAMP data type. Is it the SQL timestamp or real datetime value?

And the conversion from Nvarchar to varchar is not necessary:
alter PROCEDURE [dbo].[GetHistorianDataByField]
      @InputColumnName nvarchar(30),
      @StartDate nvarchar(30),
      @EndDate nvarchar(30)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      DECLARE @sql nvarchar(max)
      SET @sql = 'SELECT ' + @InputColumnName + ' FROM HistorianData '
                 + ' WHERE [TIMESTAMP] >= ' + @StartDate
     EXEC(@sql)
END
 
 
DECLARE @return_value int
 
EXEC    @return_value = [dbo].[GetHistorianDataByField]
                @InputColumnName = N'[PI353005]',
                @StartDate = N'2008-01-01 00:00:00',
                @EndDate = N'2009-01-01 00:00:00'
 
SELECT  'Return Value' = @return_value
 
GO

Open in new window

0
 
rwallacejAuthor Commented:
Thanks for help,

TIMESTAMP is the SQL datetime
0
 
rwallacejAuthor Commented:
I'll try these tomorrow
0
 
pcelbaCommented:
Almost alll our examples should work for you.
0
 
Mark WillsTopic AdvisorCommented:
No need to convert @startdate to datetime - it is already a datetime, but to string it in, should be an understood character string...

e.g.



declare @sql varchar(1000)
declare @startdate datetime

set @startdate = getdate()

set @sql = 'select case when getdate() - 1 > '''   convert(varchar,@startdate,120)   ''' then ''more'' else ''less'' end'

print @sql
exec(@sql)


ALTER PROCEDURE [dbo].[GetHistorianDataByField]
      @InputColumnName nvarchar(30),
      @StartDate datetime,
      @EndDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      DECLARE @sql nvarchar(max)
      SET @sql = 'SELECT '   @InputColumnName   ' FROM HistorianData '
                   ' WHERE [TIMESTAMP] >= '''   CONVERT(varchar, @StartDate,120)  ''''
      EXEC(@sql)
END
GO 
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[GetHistorianDataByField]
		@InputColumnName = N'[PI353005]',
		@StartDate = N'2008-01-01 00:00:00',
		@EndDate = N'2009-01-01 00:00:00'
 
SELECT	'Return Value' = @return_value
 
GO

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Interesting, there were plus signs in that example and the attachment when I ran it before....

the two lines of particular interest are :

set @sql = 'select case when getdate() > '''   convert(varchar,@startdate,120)   ''' then ''more'' else ''less'' end'

and in the proc:

                   ' WHERE [TIMESTAMP] >= '''   CONVERT(varchar, @StartDate,120)  ''''


0
 
Mark WillsTopic AdvisorCommented:
Did it again !!!

set @sql = 'select case when getdate() > ''' + convert(varchar,@startdate,120) + ''' then ''more'' else ''less'' end'

                 + ' WHERE [TIMESTAMP] >= ''' + CONVERT(varchar, @StartDate,120) +''''
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now