| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
165 Posts |
 Posted - 08/18/2012 : 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'
|
Edited by - learning_grsql on 08/18/2012 13:41:28
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/18/2012 : 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/
|
Edited by - visakh16 on 08/18/2012 15:21:51 |
 |
|
|
bitsmed
Posting Yak Master
Denmark
107 Posts |
Posted - 08/18/2012 : 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
165 Posts |
Posted - 08/18/2012 : 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'
|
Edited by - learning_grsql on 08/18/2012 16:08:37 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/18/2012 : 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
165 Posts |
Posted - 08/18/2012 : 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? |
Edited by - learning_grsql on 08/18/2012 16:39:06 |
|
|
|
bitsmed
Posting Yak Master
Denmark
107 Posts |
Posted - 08/18/2012 : 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
India
48076 Posts |
Posted - 08/18/2012 : 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/
|
Edited by - visakh16 on 08/18/2012 16:51:24 |
|
|
| |
Topic |
|
|
|
want 2 input different date format in parameter(sp
