convert datetime to varchar

Otana,

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.

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'

to be precise
convert( varchar(10), @presentdate , 101 )

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.

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)*/

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

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.

<< 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

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

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 ?

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.

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.

Experts,

I'll report and hopefully other experts will join.

Thanks.