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.