want 2 input different date format in parameter(sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

want 2 input different date format in parameter(sp

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 13:38:40

Hi,

I have created a stored procedure as below. The date has varchar(50) data type in the table and stored in the format dd/mm/yyyy.

Now I want to display them as 'yyyymmdd' and even for parameters I want them to enter as 'yyyymmdd'


create procedure procedurename
@startdate varchar(8)
@enddate varchar(8)
As
Begin
select convert(varchar(8),convert(datetime,date,103),112) as Date, sum(qty), sum(qty2), sum(qty3) from mytable
where date between @startdate and @enddate
group by date

execute procedurename @startdate = '20120801',  @enddate = '20120831'

if I enter parameter in the format 'yyyymmdd' it doesn't display output. I have to still enter parameter in old format 'dd/mm/yyyy' to see the results. How can I make it to format 'yyyymmdd'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 15:20:12

it same way as i suggested here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177987

also its a really good practice to make the parameter datatype as varchar when you know they deal with dates!
it calls for unwanted conversion logics in code and make mainpulations complicated if you make them as varchar

also based on whether your date field in table has time part, you might have to change the logic as



create procedure procedurename
@startdate datetime,
@enddate datetime
As
Begin
select [Date], sum(qty), sum(qty2), sum(qty3) from mytable
where [date] >= @startdate 
and [date]< dateadd(dd,1,@enddate)
group by date

I will never do formatting in sql and will do it at front end as long as i can as it can be very easy to do it at front end using formatting functions
Making formatting at code behind causes issues when you want to do further date manipulations using returned values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-08-18 : 15:24:38

You should consider changing the date field from varchar to date, along with indexing this field, for speed purpose.

To get your sql to work with the current layout, you need to do the convert thing, in the where clause aswell.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 16:07:27

Thanks for your valuable suggestions. I will try to follow that. But in this case I already tried in the beginning modifying the datatype to datetime (sql server 2008), but I was getting error something like 'cannot change datatype' so I left it as varchar

And as to your reply same way you suggested before...I already tried that before I posted..but I was getting error "incorrect syntax near @startdate' i.e. line where I input date parameter ...'execute procedure @startdate = '

My code was as below :


create procedure procedurename
@startdate varchar(8)
@enddate varchar(8)
As
Begin
select convert(varchar(8),convert(datetime,date,103),112) as Date, sum(qty), sum(qty2), sum(qty3) from mytable
where date between convert(varchar(8),convert(@startdate,103),112) and convert(varchar(8),convert(@enddate,103),112)
group by date

execute procedurename @startdate = '20120801',  @enddate = '20120831'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 16:15:32

quote:
Originally posted by learning_grsql

Thanks for your valuable suggestions. I will try to follow that. But in this case I already tried in the beginning modifying the datatype to datetime (sql server 2008), but I was getting error something like 'cannot change datatype' so I left it as varchar

And as to your reply same way you suggested before...I already tried that before I posted..but I was getting error "incorrect syntax near @startdate' i.e. line where I input date parameter ...'execute procedure @startdate = '

My code was as below :

create procedure procedurename
@startdate varchar(8)
@enddate varchar(8)
As
Begin
select convert(varchar(8),convert(datetime,date,103),112) as Date, sum(qty), sum(qty2), sum(qty3) from mytable
where date between convert(varchar(8),convert(@startdate,103),112) and convert(varchar(8),convert(@enddate,103),112)
group by date
End
go

execute procedurename @startdate = '20120801', @enddate = '20120831'

you're missing an end

Also please take away the convert on select and return field as date itself as i suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-18 : 16:37:58

well, it still didn't work but after I changed as below, it was working


where convert(varchar(8),convert(@startdate,103),112) between @startdate and @enddate
group by date
end 
go

execute procedurename @startdate = '20120801', @enddate = '20120831'

Thank you visakh16 but i don't understand your suggestion "remove convert on select and return field as date itself" . Do you mean i shall convert date column to datetime(datatype) as you suggested before?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-08-18 : 16:39:54

shouldn't the where clause be:


where convert(varchar(8),convert([date],103),112) between @startdate and @enddate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 16:49:39

quote:
Originally posted by learning_grsql

well, it still didn't work but after I changed as below, it was working

where convert(varchar(8),convert(@startdate,103),112) between @startdate and @enddate
group by date
end
go

execute procedurename @startdate = '20120801', @enddate = '20120831'

Thank you visakh16 but i don't understand your suggestion "remove convert on select and return field as date itself" . Do you mean i shall convert date column to datetime(datatype) as you suggested before?

this is not what i suggested . see my where clause


create procedure procedurename
@startdate datetime,
@enddate datetime
As
Begin
select [Date], sum(qty), sum(qty2), sum(qty3) from mytable
where [date] >= @startdate 
and [date]< dateadd(dd,1,@enddate)
group by date
end
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources