Can I use the "SET DATEFORMAT 'dmy'" with in a View?

See more:

SQL-Server

SQL-Server-2008

Hi Everyone,

Can any one please tell me whether I can use SET DATEFORMAT 'dmy' with in a VIEW or is there any trick to create a view by using DATEFORMAT 'dmy'.

Because with in the View it is not giving the permission to use SET DATEFORMAT 'dmy'

CREATE VIEW dbo.vw_Test
AS  
SET DATEFORMAT 'dmy'
SELECT  .... FROM TableName

I improved my question

[edit, extra info]
Actually I had wrong conception on this DATEFORMAT. Actually there is field for activation date in my data base that can contain NVARCHAR datatypes. The table contains more than 10 lakhs of data. So the execution time of the stored proc as approximately 1.45 minutes and giving time out error. So to reduce it I need to take it in View. Thats why I am trying too. So, if there is any other way please help me out of this situation.
[/edit]

--=============
--Table
--===============

CREATE TABLE [dbo].[tbAD_Jobg8Adverts](
	[iJobg8AdvertsID] [bigint] IDENTITY(1,1) NOT NULL,
	[JobReference] [nvarchar](1000) NULL,
	[ClientReference] [nvarchar](200) NOT NULL,
	[Classification] [nvarchar](200) NULL,
	[SubClassification] [nvarchar](200) NULL,
	[Position] [nvarchar](200) NULL,
	[Description] [nvarchar](max) NULL,
	[Location] [nvarchar](200) NULL,
	[Area] [nvarchar](200) NULL,
	[PostCode] [nvarchar](50) NULL,
	[Country] [nvarchar](200) NULL,
	[EmploymentType] [nvarchar](200) NULL,
	[StartDate] [nvarchar](200) NULL,
	[Duration] [nvarchar](200) NULL,
	[WorkHours] [nvarchar](200) NULL,
	[VisaRequired] [nvarchar](255) NULL,
	[PayPeriod] [nvarchar](200) NULL,
	[PayAmount] [nvarchar](200) NULL,
	[PayMinimum] [nvarchar](50) NULL,
	[PayMaximum] [nvarchar](50) NULL,
	[Currency] [nvarchar](50) NULL,
	[PayAdditional] [nvarchar](200) NULL,
	[Contact] [nvarchar](200) NULL,
	[ApplicationFormXML] [nvarchar](max) NOT NULL,
	[JobSource] [nvarchar](200) NULL,
	[AdvertiserName] [nvarchar](200) NULL,
	[AdvertiserType] [nvarchar](200) NULL,
	[sVisitorUserIDs] [varchar](max) NULL,
	[isActive] [bit] NULL,
	[ActivationDate] [nvarchar](200) NULL, --This is to be casted
	[ExpiryDate] [nvarchar](200) NULL,
	[JobUrl] [nvarchar](1000) NOT NULL,
	[AdditionalInfo] [nvarchar](4000) NULL,
	[JobExternalLink] [varchar](1000) NULL,
 CONSTRAINT [PK_Jobg8Adverts] PRIMARY KEY NONCLUSTERED 
(
	[iJobg8AdvertsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbAD_Jobg8Adverts] ADD  CONSTRAINT [DF_tbAD_Jobg8Adverts_isActive]  DEFAULT ((1)) FOR [isActive]
GO

ALTER TABLE [dbo].[tbAD_Jobg8Adverts] ADD  DEFAULT ('') FOR [JobUrl]
GO

--=====================
--View
--======================
-- SET DATEFORMAT 'dmy'; SELECT * FROM vwExternalJob_OrderByActivationDateDesc    
ALTER VIEW [dbo].[vwExternalJob_OrderByActivationDateDesc]    
AS  
SELECT    
  ROW_NUMBER() OVER            
   (ORDER BY (CAST((CAST(ActivationDate AS CHAR(10))) AS DATETIME)) DESC) AS RowNumber,         
  iJobg8AdvertsID,     
  JobReference,     
  ClientReference,     
  Classification,     
  SubClassification,     
  Position,     
  [Description],        
  Location as LocationOnly,     
  Area,     
  PostCode,     
  Country,    
  (dbo.ufn_GetJobg8JobLocation(Area,Location,Country)) AS Location,     
  EmploymentType,     
  ActivationDate,    
  Duration,     
  WorkHours,     
  VisaRequired,     
  PayPeriod,        
  ([dbo].[ufn_GetJobg8JobSalary](PayAmount,PayMinimum,PayMaximum, Currency,PayPeriod)) AS PayAmount,     
  PayMinimum,     
  PayMaximum,     
  Currency,     
  PayAdditional,     
  Contact,     
  ApplicationFormXML,     
  JobSource,     
  AdvertiserName,        
  AdvertiserType,     
  sVisitorUserIDs,     
  IsActive,     
  ExpiryDate,     
  JobURL,     
  ([dbo].[ufn_GetExternalJobResponseCounts](iJobg8AdvertsID,'SUCCESS')) AS ResponseCount        
FROM dbo.tbAD_Jobg8Adverts    
WHERE IsActive=1
--================
--Query
--================
ALTER PROCEDURE [dbo].[usp_GetExternalJobsForAdmin]    
	@PageSize			INT,    
	@CurrentPage		INT      
AS    
BEGIN    
	DECLARE @UpperBand INT, @LowerBand INT, @Pages INT                       
	SET @LowerBand  = (@CurrentPage - 1) * @PageSize + 1              
	SET @UpperBand  = (@CurrentPage * @PageSize)       
	SET DATEFORMAT 'dmy' 
	BEGIN
		SELECT @Pages = COUNT(*) FROM dbo.vwExternalJob_OrderByActivationDateDesc
		SELECT	RowNumber,     
				iJobg8AdvertsID, 
				JobReference, 
				ClientReference, 
				Classification, 
				SubClassification, 
				Position, 
				[Description],    
				LocationOnly, 
				Area, 
				PostCode, 
				Country,
				Location, 
				EmploymentType, 
				ActivationDate AS StartDate, 
				Duration, 
				WorkHours, 
				VisaRequired, 
				PayPeriod,    
				PayAmount, 
				PayMinimum, 
				PayMaximum, 
				Currency, 
				PayAdditional, 
				Contact, 
				ApplicationFormXML, 
				JobSource, 
				AdvertiserName,    
				AdvertiserType, 
				sVisitorUserIDs, 
				IsActive,
				ExpiryDate, 
				JobURL, 
				ResponseCount, 
				@LowerBand AS StartIndex,     
				CASE WHEN @UpperBand > @Pages THEN @Pages ELSE @UpperBand END AS EndIndex, 
				@Pages AS Total, 
				@CurrentPage AS CurrentPage     
		FROM	dbo.vwExternalJob_OrderByActivationDateDesc     
		WHERE	RowNumber BETWEEN @LowerBand AND @UpperBand
	END      
END

Thanks in advance.