Convert a time field in the select statement of the query

Hi,

I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.

Can anyone help me please?

Thx

I'm not entirely sure what you're trying to get at, but SQL Server has the following function that will get you the current UTC time:

GETUTCDATE()

If you are trying to convert the time from the timestamp to the corresponding local timestamp value, you would need to know the offset and add this time to the existing field.

Hope this helps some.

Simone

|||

Yeah!! That's the solution...... 2 weeks of deep frustrations and just such a simple solution..... wow!

Thx a lot!!!

|||

No problem, glad it worked for you. If you mark this as the answer it will better help others. Thanks.

Simone

sqlsql

CONTAINSTABLE and local TABLE variables

Hi I have a stored procedure that uses local variables of type TABLE, is it possible to use CONTAINSTABLE on these as it appears to not be working correctly. The CONTAINSTABLE code is at the very bottom of this long piece of code.

CREATE PROCEDURE dbo.SearchJobs
@.STRING varchar(6000),
@.Longitude float,
@.Latitude float,
@.Distance int,
@.Category int
AS

DECLARE @.SEARCHTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50),
SearchColumn text)

DECLARE @.OUTPUTTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50))

DECLARE @.Cat as varchar(200)
IF (NOT @.Category is NULL)
BEGIN
SET @.Cat = 'AND JobCategoryId = ' + @.Category
END
ELSE
BEGIN
SET @.Cat = ''
END

-- This does not calculate exact coordinate distances. It is designed to find all jobs
-- within a certain distance of a reference point. It uses a sqare box for speed as
-- opposed to calculationg a cirecular reference.
IF (NOT @.Longitude is NULL)
BEGIN
EXEC (' INSERT INTO @.SEARCHTABLE SELECT JobId, Type, PromotionCode, Title, JobCategoryId, [Description], ConditionOfEmployment, DollarAmount, DollarType, DateFrom, DateTo, ResumeEmail,
Phone, AreaCode, AddressId, Street, Suburb, PostCode, State, Longitude, Latitude, Positions, EmployerId, Filled, FilledBy, EmployerVisible,
EmployeeVisible, AdditionalSearchString, Street2, Suburb2, PostCode2, State2, Longitude2, Latitude2, AddressId2, Reference, (str(Title) + '' '' + str(Suburb) + '' '' + str(Street) + '' '' + str(PostCode) + CAST([Description] AS varchar))
FROM Job
WHERE (Longitude < (@.Longitude + @.Distance)) AND (Longitude > (@.Longitude - @.Distance)) AND (Latitude < (@.Latitude + @.Distance)) AND (Latitude > (@.Latitude - @.Distance))' + @.Cat)

END
ELSE
BEGIN
EXEC('INSERT INTO @.SEARCHTABLE SELECT * FROM Job WHERE 1 ' + @.Cat)
END

IF(LEN(@.STRING) > 0)

BEGIN
DECLARE @.SearchString varchar(8000)
SET @.SearchString = ''
DECLARE @.INDEX INT
DECLARE @.SLICE nvarchar(4000)

SELECT @.INDEX = 1
DECLARE @.IDCounter int
SET @.IDCounter = 0
IF @.String IS NULL RETURN
WHILE @.INDEX !=0
BEGIN
SELECT @.INDEX = CHARINDEX(' ' ,@.STRING)
IF @.INDEX !=0
BEGIN
SELECT @.SLICE = LEFT(@.STRING,@.INDEX - 1)
END
ELSE
BEGIN
SELECT @.SLICE = @.STRING
--INSERT INTO @.SEARCHTERMS (searchterm) VALUES(@.SLICE)
IF @.SearchString = ''
BEGIN
SET @.SearchString = @.SLICE
END
ELSE
BEGIN
SET @.SearchString = @.SearchString + ' OR ' + @.SLICE
END


SET @.IDCounter = @.IDCounter + 1
SELECT @.STRING = RIGHT(@.STRING,LEN(@.STRING) - @.INDEX)
IF LEN(@.STRING) = 0 BREAK
END

END
END

EXEC sp_fulltext_table @.SEARCHTABLE

SELECT FT_TBL.JobId, FT_TBL.Type, FT_TBL.PromotionCode, FT_TBL.Title, FT_TBL.JobCategoryId, FT_TBL.[Description], FT_TBL.ConditionOfEmployment, FT_TBL.DollarAmount, FT_TBL.DollarType, FT_TBL.DateFrom, FT_TBL.DateTo, FT_TBL.ResumeEmail,
FT_TBL.Phone, FT_TBL.AreaCode, FT_TBL.AddressId, FT_TBL.Street, FT_TBL.Suburb, FT_TBL.PostCode, FT_TBL.State, FT_TBL.Longitude, FT_TBL.Latitude, FT_TBL.Positions, FT_TBL.EmployerId, FT_TBL.Filled, FT_TBL.FilledBy, FT_TBL.EmployerVisible,
FT_TBL.EmployeeVisible, FT_TBL.AdditionalSearchString, FT_TBL.Street2, FT_TBL.Suburb2, FT_TBL.PostCode2, FT_TBL.State2, FT_TBL.Longitude2, FT_TBL.Latitude2, FT_TBL.AddressId2, FT_TBL.Reference,
KEY_TBL.RANK
FROM @.SEARCHTABLE AS FT_TBL INNER JOIN
CONTAINSTABLE (@.SEARCHTABLE,SearchColumn,
@.SearchString

) AS KEY_TBL
ON FT_TBL.JobId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

The deprecated sp_fulltext_table operates on a table that exists in the database, see sp_fulltext_table (Transact-SQL) in Books Online.

Indexes cannot be created explicitly on table variables. See table (Transact-SQL) in Books Online.