convert datetime to varchar : convert, datetime, 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.

04.01.2005 at 03:34AM PST, ID: 13679929

Rank: Guru

Otana:

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'

04.01.2005 at 03:42AM PST, ID: 13679966

Rank: Master

just as You write
convert( varchar(10), getdate(), 101 )
:)

Assisted Solution

04.01.2005 at 03:51AM PST, ID: 13680007

Rank: Master

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

04.01.2005 at 03:52AM PST, ID: 13680013

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.

04.01.2005 at 03:56AM PST, ID: 13680054

SQL_Stu:

Where do you populate @PresentDay & @PreviousDay? Do you repeat the select statements within a loop? Other than that you wouldn't expect the SP run-time to expand by that much!!

Assisted Solution

04.01.2005 at 03:58AM PST, ID: 13680067

Rank: Master

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

04.01.2005 at 04:00AM PST, ID: 13680075

Rank: Master

This sometimes happen with local variables.

What type is date1 column of your table ? If it is datetime, try this:

CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay datetime,
@PresentDay datetime
)
AS

select * from table1 where date between @previousday and @presentday

Go

CREATE PROCEDURE sproc_UpdateTemp
AS

DECLARE @PreviousDay datetime
DECLARE @PresentDay datetime

select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )

exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay

Go

I know that it seems strange, but very often this approach helps

Accepted Solution

04.01.2005 at 04:04AM PST, ID: 13680094

Rank: Master

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

04.01.2005 at 04:14AM PST, ID: 13680139

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.

04.01.2005 at 04:23AM PST, ID: 13680180

Rank: Guru

KarinLoos:

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

04.01.2005 at 04:31AM PST, ID: 13680227

Rank: Master

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

04.01.2005 at 04:35AM PST, ID: 13680252

Rank: Master

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 ?

04.01.2005 at 04:42AM PST, ID: 13680302

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.

04.01.2005 at 05:01AM PST, ID: 13680448

Rank: Master

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.

04.01.2005 at 05:44AM PST, ID: 13680743

fds_fatboy:

lynnton

Why are you using varchar instead of datetime datatype? If it is just to lose the time part of GetDate it is much more efficient convert it using a float.

Something like

select cast(floor(convert( float,getdate())) as datetime)

returns you today's date with the time part set to 00:00:00.

You could create a UDT like this:

create function dbo.fnDateFromDateTime(@datetime datetime)
returns datetime
AS
begin
return cast(floor(convert( float,@datetime)) as datetime)
end

And call it like this:

select dbo.fnDateFromDateTime(GetDate())

Assisted Solution

04.01.2005 at 07:45AM PST, ID: 13681726