Convert datetime to varchar

Question

Convert datetime to varchar

sandy-833685

SSC Eights!

Points: 919
More actions
September 27, 2010 at 12:19 pm

#387876

Hi i am having table with column name time data type as datetime
And when i try to execute the select statement with convert like
select * from tablename
where convert(varchar(10),time,103)<'06/06/2010'
as i need to get the all data which is less than the given date but when i run this query it gives all the rows presented in table and not with the given in where clause is there any syntax error or i am doing some mistake but if i run with syntax
select * from tablename
where time<'2010-06-06 11:15:28'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

sandy-833685 (9/27/2010)
Hi i am having table with column name time data type as datetime
And when i try to execute the select statement with convert like
select * from tablename
where convert(varchar(10),time,103)<'06/06/2010'
as i need to get the all data which is less than the given date but when i run this query it gives all the rows presented in table and not with the given in where clause is there any syntax error or i am doing some mistake but if i run with syntax
select * from tablename
where time<'2010-06-06 11:15:28'

Why are you trying to convert the date to a string if its only to match a hardcoded value in your where clause?

There are three big problems with doing it this way:

1) Its unnecessary

2) Performing a convert on your column in a where clause will cause a table scan, making it perform slow.

3) You are performing a string comparison instead of a date comparison, meaning that it would only be equivalent if you formatted the date in yyyy/mm/dd, because it is looking at the characters right to left.

The simple where time < syntax is much better. If you compare a date to a hardcoded date string, sql server will be clever enough to interpret it.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

sandy-833685 (9/27/2010)
Hi i am having table with column name time data type as datetime
And when i try to execute the select statement with convert like
select * from tablename
where convert(varchar(10),time,103)<'06/06/2010'
as i need to get the all data which is less than the given date but when i run this query it gives all the rows presented in table and not with the given in where clause is there any syntax error or i am doing some mistake but if i run with syntax
select * from tablename
where time<'2010-06-06 11:15:28'

There can only be a single sound advice regarding your request: DON'T

As always, don't perform a function on a column in a where clause, if you can provide the correct format of a declared variable !

If you insist on using a conversion function, convert the variable !

select * from tablename

where time < convert(datetime,'06/06/2010', formatcode ) -- see books online for your correct format !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sandy-833685 SSC Eights! Points: 919 More actions · Answer 3

Thx,

But its not hardcoded its an value which is stored in variable which is having a datatype as datetime. and after going through certain select and execution stored procedure.And i am using this variable in bcp like

declare @sql varchar(2000),@date datetime

Set @date='2009-06-06 11:17:00'

Set @sql ='Select * from tablename where time<'+@date+'order by time'

Exec (SQL)

And it gives an error converting datetime from character string.

But when i execute same statement with convert or cast to varchar it works but output is not accurate as disscussed.

Please let me know is there any other alternative to work around or there some mistake happening from my end.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

sandy-833685 (9/27/2010)
Thx,
But its not hardcoded its an value which is stored in variable which is having a datatype as datetime. and after going through certain select and execution stored procedure.And i am using this variable in bcp like
declare @sql varchar(2000),@date datetime
Set @date='2009-06-06 11:17:00'
Set @sql ='Select * from tablename where time<'+@date+'order by time'
Exec (SQL)
And it gives an error converting datetime from character string.
But when i execute same statement with convert or cast to varchar it works but output is not accurate as disscussed.
Please let me know is there any other alternative to work around or there some mistake happening from my end.

Please be straight forward with your questions, altering them during a forum thread will only cause frustration for the ones trying to help out.

Avoid dynamic sql !!

declare @date datetime

Set @date='2009-06-06 11:17:00'

Select * from tablename where time< @date order by time;

When altering datetime into a string, always use the convert function to specify your correct datetime format.

Don't rely on the datetime format setting of your windows install or client settings !!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Nevyn SSChampion Points: 14210 More actions · Answer 5

sandy-833685 (9/27/2010)
Thx,
But its not hardcoded its an value which is stored in variable which is having a datatype as datetime. and after going through certain select and execution stored procedure.And i am using this variable in bcp like
declare @sql varchar(2000),@date datetime
Set @date='2009-06-06 11:17:00'
Set @sql ='Select * from tablename where time<'+@date+'order by time'
Exec (SQL)
And it gives an error converting datetime from character string.
But when i execute same statement with convert or cast to varchar it works but output is not accurate as disscussed.
Please let me know is there any other alternative to work around or there some mistake happening from my end.

You arent getting the error because "time" needs to be converted. You are getting the error because your dynamic sql does not have quotes around the date you are comparing time to.

And as has been pointed out, unless there is even more you're not telling us, you don't even need dynamic sql here.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

sandy-833685 (9/27/2010)
Hi i am having table with column name time data type as datetime
And when i try to execute the select statement with convert like
select * from tablename
where convert(varchar(10),time,103)<'06/06/2010'
as i need to get the all data which is less than the given date but when i run this query it gives all the rows presented in table and not with the given in where clause is there any syntax error or i am doing some mistake but if i run with syntax
select * from tablename
where time<'2010-06-06 11:15:28'

Hi,

Have to tried this

declare @sql varchar(2000),@date varchar(20)

Set @date='2009-06-06 11:17:00'

Set @sql ='Select * from sys.sysobjects where crdate< '''+@date+''' order by crdate'

Exec (@sql)

We should not convert datetime to Varchar formate.But as per your request it has been done

Thanks

Parthi

Thanks
Parthi