?
Solved

convert datetime to varchar

Posted on 2005-04-01
19
Medium Priority
?
136,179 Views
Last Modified: 2012-06-27
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
Comment
Question by:lynnton
  • 5
  • 4
  • 4
  • +5
19 Comments
 
LVL 11

Assisted Solution

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

Author Comment

by:lynnton
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

by:Otana
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Assisted Solution

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

Expert Comment

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

Author Comment

by:lynnton
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.
0
 
LVL 7

Assisted Solution

by:SQL_Stu
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

by:mokule
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

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

by:Jan Franek
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

by:lynnton
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

by:KarinLoos
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

by:mokule
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

by:Jan Franek
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

by:lynnton
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

by:Jan Franek
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

by:fds_fatboy
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

by:lynnton
ID: 13681726
Experts,

I'll report and hopefully other experts will join.

Thanks.
0
 

Expert Comment

by:mohitbhatia5
ID: 24419241
hiii...

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

thx....
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question