query assistance

I have a field in a table that the datatype is varchar
the data stored in the field is a date in the format of 7/19/2007
I need to run a query that will give me all teh records that is of a
certain date or newer.
teh query I am running is
Select * from table1 where {do rec] >= '07/19/2007'
what I get is pretty much everything. items dated 7/31/2006 etc...
how can I run this query correctly?
The reason the field is a varchar is becasue I don't want the 'time' in the
field along with the date.
thanks in advance.The obvious question is why you stored the values as varchar instead of date
time. I suggest you fix
the design before it causes more trouble, bad performance and grief.
Having said that, you can convert the value to datetime (in the WHERE clause
) before you do the
conversion. Since you use a date format which isn't language neutral (assumi
ng the values are stored
in the format m/d/yyyy), you need to use CONVERT (with a proper formatting c
ode) instead of CAST,
and because the conversion of the column value to datetime, you need to be p
repared for the query to
be slow. Something like:
WHERE CONVERT(datetime, dtcol, 101) > '20070719'
I suggest you give below some time and thoughts:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
>I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a certa
in date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in th
e field along with the
> date.
>
> thanks in advance.
>
>[/vbcol]|||I tried going to your link, but the page cannot be found
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
> The obvious question is why you stored the values as varchar instead of
> datetime. I suggest you fix the design before it causes more trouble, bad
> performance and grief.
> Having said that, you can convert the value to datetime (in the WHERE
> clause) before you do the conversion. Since you use a date format which
> isn't language neutral (assuming the values are stored in the format
> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
> of CAST, and because the conversion of the column value to datetime, you
> need to be prepared for the query to be slow. Something like:
> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
> I suggest you give below some time and thoughts:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Johnfli" <john@.ivhs.us> wrote in message
> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>|||On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a
> certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in th
e
> field along with the date.
> thanks in advance.
I do agree that you really should change your database design to use a
proper data type. This will make your life easier and not require
some of the conversions. Storing the time in the database shouldn't
cause you any grief and you shouldn't have any issues with the query
you specified
Of course your workaround will be to utilize the convert function in
your query.|||Perhaps there was a temporary IP hiccup somewhere. I just re-tried my link a
nd it work fine for me:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:etTzacV1HHA.4344@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
>I tried going to your link, but the page cannot be found
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
>[/vbcol]|||I am trying to change the field type to DateTime, but I can not figure out
how.
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do agree that you really should change your database design to use a
> proper data type. This will make your life easier and not require
> some of the conversions. Storing the time in the database shouldn't
> cause you any grief and you shouldn't have any issues with the query
> you specified
> Of course your workaround will be to utilize the convert function in
> your query.
>|||Here's how such a ALTER TABLE statement could look like:
ALTER TABLE tbl
ALTER COLUMN col datetime
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:%23meH$Jf1HHA.3788@.TK2MSFTNGP02.phx.gbl...[vb
col=seagreen]
>I am trying to change the field type to DateTime, but I can not figure out
> how.
>
>
> "acorcoran" <acorcoran@.gmail.com> wrote in message
> news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
>[/vbcol]