Problem with convert(varchar,datetime,101)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with convert(varchar,datetime,101)

Author

Topic

hlp4al
Starting Member

5 Posts

Posted - 2009-06-16 : 02:34:09

Hi..

Recently i encountered a problem with convert(varchar,col1,101)
i.e.
select * from tablename where convert(varchar,col1,101)>=convert(varchar,'01/10/2008',101) and convert(varchar,col1,101)<=convert(varchar,'06/10/2009',101)
from the above query i am getting the result from 01/10/2009 not from 01/10/2008.

Please provide your valuable solution to me

Thanks and regards
hlp4al

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:08:30

Why are you converting a datetime column to varchar? And with Month stored in the beginning of string?

This is how you should write your query!

select * from tablename where col1 >= '20080110' and col1 < '20090611'

E 12°55'05.63"
N 56°04'39.26"

hlp4al
Starting Member

5 Posts

Posted - 2009-06-16 : 03:22:38

thanks for u r quick reply
hi..

col1 is datetime data type in database. I just want to check only date from the col1, because of that only i took varchar.
I want the <= condition for date in where condition

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:42:58

No, you want not to!
If Col1 also includes time (and it does 00:00:00.000 as default), you want an OPEN-ENDED date search criteria as shown in my suggestion.

select * from tablename where col1 >= '20080110' and col1 < '20090611'

Will get you all records from January 10, 2008 at 12 am, to June 11, 2009 12 am (not included).

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:43:42

Try my suggestion and see if you get the wanted records back...

E 12°55'05.63"
N 56°04'39.26"

hlp4al
Starting Member

5 Posts

Posted - 2009-06-16 : 04:52:31

thanks for u r suggestion
Your query giving details, but bow i can get only date field from datetime coolumn.

from col1=2008-08-29 12:59:29.000 and i want only 2008-08-29
and i want to check this individually , date separately and time separately.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:00:19

Why?
Please provide some sample data and expected output.

What I displayed for you is a filtering technique which will giver the records you want, as a base for further processing.
All other manipulation is formatting for reporting issues only.

Dividing date and time into two different columns make filtering much harder to do.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Col1), 0) AS onlyDate,
DATEADD(DAY, DATEDIFF(DAY, Col1, 0), Col1) AS onlyTime
FROM Table1
WHERE Col1 >= '20080110' AND Col1 < '20090611'

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 05:02:25

SELECT CONVERT(VARCHAR(10), Col1, 101) AS onlyDate,
FROM Table1
WHERE Col1 >= '20080110' AND Col1 < '20090611'

E 12°55'05.63"
N 56°04'39.26"

hlp4al
Starting Member

5 Posts

Posted - 2009-06-16 : 07:54:28

In my database col1 have date as 2008-08-29 12:59:29.000
SELECT CONVERT(VARCHAR(10), col1, 101) AS onlyDate
FROM table1
WHERE col1 >= '08/29/2009' AND col1< '09/05/2009'

i can provide conditional dates either in mm/dd/yyyy or dd/mm/yyyy
but the above query is not giving any results.

Please provide some help to me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 08:01:11

First of all, your posted sample data is for 2008, and your query is for 2009.

Does this give you some result?

SELECT CONVERT(VARCHAR(10), col1, 101) AS onlyDate
FROM table1
WHERE col1 >= '20080829' AND col1 < '20090905'

E 12°55'05.63"
N 56°04'39.26"

hlp4al
Starting Member

5 Posts

Posted - 2009-06-16 : 08:19:17

ok.. thanks for your reply..

lathangi.ch
Starting Member

6 Posts

Posted - 2009-11-26 : 00:41:28

Hi,
I am in the similar situation.This is the first time i am having a problem in comparing two datetime values.I am trying to develop an ageing report for my data.Here is the sample data..

Name Amt <30 30-60 60-90 90-365 1yr-2yr >2yr
---------------------------------------------------------------
John $25.50 10.0 - 15.50 - - -
Smith $17.25 - 10.00 - 7.25 - -
Bill $10.00 10.0 - - - - -

case 1(Under30):
-------------
For this my query would be like
select name, amt
....
..
from tablename
where txndate < getdate() - 30

case 2(above 30 and below 60):
------------------------------
select name, amt
....
..
from tablename
where convert(varchar,txndate,106) between convert(varchar,getdate()-30,106) and convert(varchar,getdate()- 59,106)

The above query worked for 30-60 and 60-90. But i don't know what went wrong with the code it started giving null data for 90-365 and for the other two cases.

Any advice is much appreciated.

Thanks in advance,
Lathangi.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:33:12

quote:
Originally posted by lathangi.ch

Hi,
I am in the similar situation.This is the first time i am having a problem in comparing two datetime values.I am trying to develop an ageing report for my data.Here is the sample data..

Name Amt <30 30-60 60-90 90-365 1yr-2yr >2yr
---------------------------------------------------------------
John $25.50 10.0 - 15.50 - - -
Smith $17.25 - 10.00 - 7.25 - -
Bill $10.00 10.0 - - - - -

case 1(Under30):
-------------
For this my query would be like
select name, amt
....
..
from tablename
where txndate < getdate() - 30

case 2(above 30 and below 60):
------------------------------
select name, amt
....
..
from tablename
where convert(varchar,txndate,106) between convert(varchar,getdate()-30,106) and convert(varchar,getdate()- 59,106)

The above query worked for 30-60 and 60-90. But i don't know what went wrong with the code it started giving null data for 90-365 and for the other two cases.

Any advice is much appreciated.

Thanks in advance,
Lathangi.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136384

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:35:33

quote:
Originally posted by hlp4al

ok.. thanks for your reply..

What is the difficulty in understanding the code
You seem to confuse yourself with date and time

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources