Solved

# convert datetime to varchar

Posted on 2005-04-01
Medium Priority
136,179 Views
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.
0
Question by:lynnton
• 5
• 4
• 4
• +5

LVL 11

Assisted Solution

Otana earned 400 total points
ID: 13679874
cast(@presentdate as varchar(20))
0

LVL 1

Author Comment

ID: 13679900
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.
0

LVL 11

Expert Comment

ID: 13679929
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'
0

LVL 17

Assisted Solution

mokule earned 400 total points
ID: 13679966
just as You write
convert( varchar(10), getdate(), 101 )
:)
0

LVL 17

Expert Comment

ID: 13680007
to be precise
convert( varchar(10), @presentdate , 101 )
0

LVL 1

Author Comment

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

Please kindly guide me what could be wrong?

Thanks.
0

LVL 7

Assisted Solution

SQL_Stu earned 400 total points
ID: 13680054
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!!
0

LVL 17

Expert Comment

ID: 13680067
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)*/
0

LVL 14

Accepted Solution

Jan Franek earned 400 total points
ID: 13680075
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
0

LVL 14

Expert Comment

ID: 13680094
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
0

LVL 1

Author Comment

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

LVL 13

Expert Comment

ID: 13680180
<< 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
0

LVL 17

Expert Comment

ID: 13680227
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
0

LVL 14

Expert Comment

ID: 13680252
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 ?
0

LVL 1

Author Comment

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

LVL 14

Expert Comment

ID: 13680448
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.
0

LVL 10

Assisted Solution

fds_fatboy earned 400 total points
ID: 13680743
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())
0

LVL 1

Author Comment

ID: 13681726
Experts,

I'll report and hopefully other experts will join.

Thanks.
0

Expert Comment

ID: 24419241
hiii...

Use cast(columnname as varchar) as 'Alias' or any data type like datetime or decimal(38,2)

thx....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll