How to add time zone to Date time field

Question

How to add time zone to Date time field

pilla.sree85

SSC Enthusiast

Points: 139
More actions
July 4, 2014 at 11:14 am

#308866

I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
Please help me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

pilla.sree85 (7/4/2014)
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
Please help me

What is your expected results based on the date passed in to the stored procedure? Are you looking for all records with that date?

If so, try this in the stored procedure:

select

* -- You should actually list the columns to be returned.

from

Date_table

where

cast(Create_TM as DATE) = @CreatedDate;

This is based on the fact that you posted your question in a SQL Server 2014 forum. This will also work in SQL Server 2008/2008 R2/2012.

pilla.sree85 SSC Enthusiast Points: 139 More actions · Answer 2

I was tried with your solution below but no luck.

select * from data_table where cast(CREAT_TM as DATE) = '2012-07-01'

i am getting error as

Type DATE is not a defined system type.

because CREAT_TM field declared as DateTime in SP and in table as well.Its value is 2012-07-01 22:46:40.000 in table.when i am trying to fetch records with @CreatedDate ( 2012-07-01) i am getting null results.

but when i am trying below select query i am getting expected results.

select * from data_table where Where CREAT_TM between '2012-07-01 00:00:00' and '2012-07-01 23:59:59'.

@CreatedDate is in between '2012-07-01 00:00:00' and '2012-07-01 23:59:59' then i am getting results.

How would i change date format to get the expected results

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

pilla.sree85 (7/11/2014)
I was tried with your solution below but no luck.
select * from data_table where cast(CREAT_TM as DATE) = '2012-07-01'
i am getting error as
Type DATE is not a defined system type.
...

What version of SQL server are you working with?

To find out, run this: SELECT @@version

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pilla.sree85 SSC Enthusiast Points: 139 More actions · Answer 4

pilla.sree85

SSC Enthusiast

Points: 139

July 11, 2014 at 5:01 am

#1728680

sql server 2005

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

pilla.sree85 (7/11/2014)
sql server 2005

SELECT *

FROM data_table

WHERE CREAT_TM >= @CreatedDate

AND CREAT_TM < DATEADD(DAY,1,@CreatedDate)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pilla.sree85 SSC Enthusiast Points: 139 More actions · Answer 6

Thanks for your help.i am getting the expected results.