?
Solved

convert datetime to varchar

Posted on 2005-04-01
19
Medium Priority
?
135,960 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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
Suggested Courses

800 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