lynnton
asked on
convert datetime to varchar
Hi Experts,
How can we convert a variable @presentdate to varchar?
Declare @presentday as datetime
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select * from table where date1 = @presentday <---------here
Thanks.
How can we convert a variable @presentdate to varchar?
Declare @presentday as datetime
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select * from table where date1 = @presentday <---------here
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select cast(getdate() as varchar(11)) will return 'Apr 1 2005'
select
cast(day(getdate()) as varchar(2)) + '/' +
cast(month(getdate()) as varchar(2)) + '/' +
cast(year(getdate()) as varchar(4))
will return '1/4/2005'
select
cast(day(getdate()) as varchar(2)) + '/' +
cast(month(getdate()) as varchar(2)) + '/' +
cast(year(getdate()) as varchar(4))
will return '1/4/2005'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to be precise
convert( varchar(10), @presentdate , 101 )
convert( varchar(10), @presentdate , 101 )
ASKER
Hi,
This just plain sucks. (excuse me)
The SP is very fast, takes 1:30 minutes to finish if I place a manual date
i.e.
dtstart between '1/1/2005' and '1/2/2005'
__________________________ __________ __________ __________ _____
unfortunately when we place
Declare Presentday as datetime
Declare Previousday as datetime
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )
__________________________ __________ __________ __________ _____
it's already 3 hours and still running with no results..
Please kindly guide me what could be wrong?
Thanks.
This just plain sucks. (excuse me)
The SP is very fast, takes 1:30 minutes to finish if I place a manual date
i.e.
dtstart between '1/1/2005' and '1/2/2005'
__________________________
unfortunately when we place
Declare Presentday as datetime
Declare Previousday as datetime
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )
__________________________
it's already 3 hours and still running with no results..
Please kindly guide me what could be wrong?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to have
dtstart between '1/1/2005' and '1/2/2005'
do
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 )) /* this gives 1/1/2005 */
dateadd( day, 1, @PresentDay ) /* this gives You 1/2/2005 (note +1 not -1)*/
dtstart between '1/1/2005' and '1/2/2005'
do
select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 )) /* this gives 1/1/2005 */
dateadd( day, 1, @PresentDay ) /* this gives You 1/2/2005 (note +1 not -1)*/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If date1 is varchar, it will look like this:
CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay varchar(10),
@PresentDay varchar(10)
)
AS
select * from table1 where date between @previousday and @presentday
Go
CREATE PROCEDURE sproc_UpdateTemp
AS
DECLARE @PreviousDay varchar(10)
DECLARE @PresentDay varchar(10)
select @PresentDay = convert( varchar(10), getdate(), 101 )
select @PreviousDay = convert( varchar(10), dateadd( day, -1, getdate() ), 101 )
exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay
Go
CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay varchar(10),
@PresentDay varchar(10)
)
AS
select * from table1 where date between @previousday and @presentday
Go
CREATE PROCEDURE sproc_UpdateTemp
AS
DECLARE @PreviousDay varchar(10)
DECLARE @PresentDay varchar(10)
select @PresentDay = convert( varchar(10), getdate(), 101 )
select @PreviousDay = convert( varchar(10), dateadd( day, -1, getdate() ), 101 )
exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay
Go
ASKER
Jan_Franek,
Yes it is datetime datatype. Sad to say, it still running 15 mintues and counting now..
mokule,
select convert( datetime, convert( varchar(10), getdate(), 101 ))
2005-04-01 00:00:00.000 <------result after running in QA
Thanks.
Yes it is datetime datatype. Sad to say, it still running 15 mintues and counting now..
mokule,
select convert( datetime, convert( varchar(10), getdate(), 101 ))
2005-04-01 00:00:00.000 <------result after running in QA
Thanks.
<< If date1 is varchar>>
<<it is datetime datatype>>
could u try:
Declare @presentday as varchar(10)
SET @PresentDay = convert( varchar(10), getdate(), 101 )
select * from table where Convert( varchar(10), date1, 101) = @presentday
<<it is datetime datatype>>
could u try:
Declare @presentday as varchar(10)
SET @PresentDay = convert( varchar(10), getdate(), 101 )
select * from table where Convert( varchar(10), date1, 101) = @presentday
lynnton
>> select convert( datetime, convert( varchar(10), getdate(), 101 ))
>> 2005-04-01 00:00:00.000 <------result after running in QA
Exactly, as it is datetime variable. This is not a problem
>> select convert( datetime, convert( varchar(10), getdate(), 101 ))
>> 2005-04-01 00:00:00.000 <------result after running in QA
Exactly, as it is datetime variable. This is not a problem
Does your table have any indices ? Can you check, what index is used when you run your procedure ? And compare it with index used when you run it manually ?
ASKER
Jan_Franek,
If you could kindly guide me how to extract the information you needed?
>>Does your table have any indices ? Can you check, what index is used when you run your procedure ? And compare it with index used when you run it manually ? <<
what does SQL server says when we place a string like : '3/30/2005' ?
does it take it as a string? varchar? datetime ?
Thanks.
If you could kindly guide me how to extract the information you needed?
>>Does your table have any indices ? Can you check, what index is used when you run your procedure ? And compare it with index used when you run it manually ? <<
what does SQL server says when we place a string like : '3/30/2005' ?
does it take it as a string? varchar? datetime ?
Thanks.
Sorry, I don't use MS SQL, so I don't know, what profiling tools you can use. However - if you can post schema of your table with all indexes, may be we could pick some good one and try to force SQL server to use it.
When you insert '3/30/2005' and use it for datetime column, SQL server will implicitly convert yout varchar into datetime.
The difference between manual execution and procedure is, that manual execution is compiled with exact knowlege of parameters, so compiler can choose best strategy for given parameters, while procedure is compiled at creation time without any information about values, that you will use when invoking it. That's probably why compiler chooses bad index and it results in poor performance.
When you insert '3/30/2005' and use it for datetime column, SQL server will implicitly convert yout varchar into datetime.
The difference between manual execution and procedure is, that manual execution is compiled with exact knowlege of parameters, so compiler can choose best strategy for given parameters, while procedure is compiled at creation time without any information about values, that you will use when invoking it. That's probably why compiler chooses bad index and it results in poor performance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Experts,
I'll report and hopefully other experts will join.
Thanks.
I'll report and hopefully other experts will join.
Thanks.
hiii...
Use cast(columnname as varchar) as 'Alias' or any data type like datetime or decimal(38,2)
thx....
Use cast(columnname as varchar) as 'Alias' or any data type like datetime or decimal(38,2)
thx....
ASKER
Do you happen to know how can we get date value only?
1/1/2005 00:00:00.000
date value:
1/1/2005
Thanks.