it returning null

Question

Post reply

it returning null

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 5, 2012 at 8:19 am

#261131

hi everyone,
when i runing below stored procedure it not returning any value
alter procedure sp_date
@dyti datetime
as
begin
select Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume from dbo.Levels
where Reading_Date=@dyti
group by Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume
end
exec sp_date '2012-05-20 00:45:36'
-------- while i using as normal slect statement
it returning the values
select Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume from dbo.Levels
where Reading_Date='2012-05-20 00:30:00'
whats the problem plss
cheers..

Viewing 12 posts - 1 through 11 (of 11 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Could be...

1) Lack of explicit SET DATEFORMAT xyz in the stored procedure.

2) Implicit convert of string 'yyyy-mm-dd hh:mm:ss' in your adhoc run isn't being treated the same in the SP.

3) Format of your ReadingDate column is not DATETIME (or is different, i.e. DATETIME2)

4) Something I've not thought of yet (likely).

Have you tried using BETWEEN instead of an explicit value, to check if it's a problem with the SP evaluating the given date in too precise a manner? i.e. '2012-01-01 03:00:00.005' is not equal to '2012-01-01 03:00:00.005' even if you mean it to be.

Try using cast(floor(cast(getdate() as float)) as datetime) on the DATETIME value you're passing in (@dyti) to bottom out the value before comparing it, or using BETWEEN as above.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

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

vinay.varaala (9/5/2012)
hi everyone,
when i runing below stored procedure it not returning any value
alter procedure sp_date
@dyti datetime
as
begin
select Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume from dbo.Levels
where Reading_Date=@dyti
group by Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume
end
exec sp_date '2012-05-20 00:45:36'
-------- while i using as normal slect statement
it returning the values
select Simmons_PanelID,Reading_Date,Fuel_Level ,Fuel_Temp,Fuel_Volume from dbo.Levels
where Reading_Date='2012-05-20 00:30:00'
whats the problem plss
cheers..

I see a comparision of apples and oranges here. In the execution of the stored procedure you use '2012-05-20 00:45:36' as the parameter entered to the procedure. When you execute the select statement itself you use '2012-05-20 00:30:00'. Two completely different values.

Perhaps if you were to provide the DDL for the tables, some sample data (as INSERT INTO statements) and the expected results based on the sample data and sample input we would be able to provide a better answer.

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 3

thanks got the error. cheers..

one more question i want to select a record of 2012-05-20 date how to teh code for dateadd()

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

vinay.varaala (9/5/2012)
thanks got the error. cheers..
one more question i want to select a record of 2012-05-20 date how to teh code for dateadd()

http://bit.ly/UrUYss

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 5

actually I was looking for

eg: when we search for books in amazon one drop box will ask for with24hrs, last week, last month, last year, I want the date coversion code pls..

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

vinay.varaala (9/5/2012)
actually I was looking for
eg: when we search for books in amazon one drop box will ask for with24hrs, last week, last month, last year, I want the date coversion code pls..

HUH???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

vinay.varaala (9/5/2012)
actually I was looking for
eg: when we search for books in amazon one drop box will ask for with24hrs, last week, last month, last year, I want the date coversion code pls..

Maybe this will help? http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

vinay.varaala (9/5/2012)
thanks got the error. cheers..
one more question i want to select a record of 2012-05-20 date how to teh code for dateadd()

This is far too vague. What are you really trying to do? How about

SELECT rows

FROM mytable m

WHERE m.datetimecolumn >= @Startdate AND m.datetimecolumn < @Enddate

EDIT: wonky post

^{“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

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 9

select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month

getdate()-- current date

my question I want the records from beginning of previous month to today ..wats the code plss i was tryng but couldn't ..

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

vinay.varaala (9/5/2012)
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
getdate()-- current date
my question I want the records from beginning of previous month to today ..wats the code plss i was tryng but couldn't ..

SELECT *

FROM Table

WHERE DateColumn >= dateadd(mm, datediff(mm, 0, getdate()) - 1, 0)

and DateColumn < getdate() + 1 -- to include the "whole" current date

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

vinay.varaala (9/5/2012)
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
getdate()-- current date
my question I want the records from beginning of previous month to today ..wats the code plss i was tryng but couldn't ..

Can you show what you've tried? It will provide a framework for a solution. It should look something like this:

SELECT

Simmons_PanelID,

Reading_Date,

Fuel_Level,

Fuel_Temp,

Fuel_Volume

FROM dbo.Levels

WHERE Reading_Date >= @Startdate

AND Reading_Date < @Enddate

^{“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