Date and Time Format's Usage

[VIJSQL01]

hi,

i am facing an issue in converting one of the date format to other.
The current date format is 1/1/2010
Required format is Jan-10

I have used the following code to convert it. Its working fine in source table not in the target table. In both tables, i have defined data type in both the tables as varchar only.

Conversion:

CONVERT(CHAR(4), convert(datetime,Period), 100) +'-'+ substring(CONVERT(CHAR(4),
convert(datetime,Period), 120),3,4)

When i execute above query separately for this table, its working fine(as shown).

select CONVERT(CHAR(4), convert(datetime,Period), 100) +'-'+ substring(CONVERT(CHAR(4),
convert(datetime,Period), 120),3,4)
from REAS_HPI_STATE_Final
order by Period desc

But when i implement the same to stored procedure its not working and its throwing an error message.

USE [REAS]
GO
/****** Object:  StoredProcedure [dbo].[NAT_Changes_SFA]    Script Date: 01/08/2011 07:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[NAT_Changes_SFA]
As Begin

declare @SQL varchar(max)

Begin
If object_id('NAT_AllChanges_SFA_Temp','U') is not null drop table NAT_AllChanges_SFA_Temp

Set @SQL= '(Insert into NAT_AllChanges_SFA_Temp as 
SELECT (CONVERT(CHAR(4), convert(datetime,Period), 100) +'-'+ substring(CONVERT(CHAR(4),
convert(datetime,Period), 120),3,4),
HPI_1M_Change * 100 , HPI_3M_Change * 100 , HPI_6M_Change * 100 , HPI_12M_Change * 100 , Home_Price_Index
FROM dbo.REAS_HPI_STATE_Final
WHERE Tier_Code = 7 AND STATE_Code = 00
ORDER BY period DESC)'
execute (@SQL)
End;
END;

Error i am getting

Msg 8117, Level 16, State 1, Procedure NAT_Changes_SFA, Line 10
Operand data type varchar is invalid for subtract operator.

Thanks, for your help.

 

[bborissov]

You must double the quotes inside the string:

Set @SQL= '(Insert into NAT_AllChanges_SFA_Temp as
SELECT (CONVERT(CHAR(4), convert(datetime,Period), 100) +
''-''+ -- HERE
substring(CONVERT(CHAR(4), convert(datetime,Period), 120),3,4),
HPI_1M_Change * 100 , HPI_3M_Change * 100 , HPI_6M_Change * 100 , HPI_12M_Change * 100 , Home_Price_Index
FROM dbo.REAS_HPI_STATE_Final
WHERE Tier_Code = 7 AND STATE_Code = 00
ORDER BY period DESC)'
..

I have to ask:
WHY???
Why Dynamic SQL?
Why you need to store date in such format?
Presentation of the data is FrontEnd task not SQL Server one.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[VIJSQL01]

Thanks for the solution. That part worked for me. The reason i am creating dynamic temporary table is, every time i execute this query(SP) , the number of columns will be varying. Say for example, if we have 384 columns this time and next time it would be 385 and so on.

Now i am facing different problem with stored procedure created. I am able to comiple the stored procedure without any errors. But when i execute that SP, its throwing a syntax error which i am not able to figure out why.

The SP which compiled properly....

USE [REAS]
GO
/****** Object:  StoredProcedure [dbo].[NAT_Changes_SFA]    Script Date: 01/08/2011 07:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[NAT_Changes_SFA]
As Begin

declare @SQL varchar(max)

Begin
If object_id('NAT_AllChanges_SFA_Temp','U') is not null drop table NAT_AllChanges_SFA_Temp

Set @SQL= 'Select * into NAT_AllChanges_SFA_Temp From
(SELECT (CONVERT(CHAR(4), convert(datetime,Period), 100) +''-''+ substring(CONVERT(CHAR(4),convert(datetime,Period), 120),3,4),
HPI_1M_Change * 100 as HPI_1M_Change, HPI_3M_Change * 100 as HPI_3M_Change,
HPI_6M_Change * 100 as HPI_6M_Change, HPI_12M_Change * 100 as HPI_12M_Change , Home_Price_Index
FROM dbo.REAS_HPI_STATE_Final
WHERE Tier_Code = 7 AND STATE_Code = 00
ORDER BY period DESC)'
execute (@SQL)
End
END;

While execution, i am getting following error...
[code]
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

[/code]

 

[markros]

You're missing an alias for the derived table, e.g.

select * into ... FROM (Select statement) as Derived

PluralSight Learning Library

 

[VIJSQL01]

i put the alias as follows, still i am getting the same issue..Alias is for the entire query or just the data selection query? I am confused..

USE [REAS]
GO
/****** Object:  StoredProcedure [dbo].[NAT_Changes_SFA]    Script Date: 01/08/2011 07:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[NAT_Changes_SFA]
As Begin

declare @SQL varchar(max)

Begin
If object_id('NAT_AllChanges_SFA_Temp','U') is not null drop table NAT_AllChanges_SFA_Temp

Set @SQL= 'Select * into NAT_AllChanges_SFA_Temp From
(SELECT (CONVERT(CHAR(4), convert(datetime,Period), 100) +''-''+ substring(CONVERT(CHAR(4),convert(datetime,Period), 120),3,4),
HPI_1M_Change * 100 as HPI_1M_Change, HPI_3M_Change * 100 as HPI_3M_Change,
HPI_6M_Change * 100 as HPI_6M_Change, HPI_12M_Change * 100 as HPI_12M_Change , Home_Price_Index
FROM dbo.REAS_HPI_STATE_Final
WHERE Tier_Code = 7 AND STATE_Code = 00
ORDER BY period DESC as NationalData)' 
execute (@SQL)
End
END;

 

[markros]

Alias for derived table, of course. In other words:

USE [REAS]
GO
/****** Object:  StoredProcedure [dbo].[NAT_Changes_SFA]    Script Date: 01/08/2011 07:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[NAT_Changes_SFA]
As Begin

declare @SQL varchar(max)

Begin
If object_id('NAT_AllChanges_SFA_Temp','U') is not null drop table NAT_AllChanges_SFA_Temp

Set @SQL= 'Select * into NAT_AllChanges_SFA_Temp From
(SELECT CONVERT(CHAR(4), convert(datetime,Period), 100) 
+''-''+ substring(CONVERT(CHAR(4),convert(datetime,Period), 120),3,4) as Period,
HPI_1M_Change * 100 as HPI_1M_Change, HPI_3M_Change * 100 as HPI_3M_Change,
HPI_6M_Change * 100 as HPI_6M_Change, HPI_12M_Change * 100 as HPI_12M_Change , Home_Price_Index
FROM dbo.REAS_HPI_STATE_Final
WHERE Tier_Code = 7 AND STATE_Code = 00) As NationalData
ORDER BY period DESC' 
execute (@SQL)
End
END;

PluralSight Learning Library

 

[VIJSQL01]

yeah, ths helped a lot.

Thanks so much.

 

[VIJSQL01]

i have a problem in exportng the query result into .xlsx format in SQL 2008.
I am not sure whether its possible to export it in .xlsx format. Because i checked it and i am able to export it in .csv and .xls format.

If possible, please let me know how to do that?

Thanks

 

[SkipVought]

If it were me getting the export from you, to use in Excel, I would move heaven and earth to discover the information related to the source and query and perform the query on your database directly from Excel in order to eliminate the 'middle man' export file et al.

In cases when I cannot 'eliminate', I run a parallel check in Excel to verify the data and to be able to run it when I choose, rather than wait on someone elses schedule or not.

Just my 2¢

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}