Date portion comparison of a datetime field

I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:

> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:

> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:

> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.

Date lookup in SSIS

How do I perform a date lookup in SSIS. I have a date with time component in it. This has to be looked-up with a table that contains only a date element.

You need convert the fields into varchar and do the comparison or you can convert both the fields to similar date formatted datetime type and do the comparison.

Thanks,

S Suresh

|||

I tried converting to varchar and it does not work well. There should be some other elegant way of doing this. To help understand the problem, I have created two tables table_1 and table_2. Table_1 is the source table with one column DateWithTime of type (datetime). Table_2 is the lookup table with columns DateSK of type (int) and another column DateAlone of type (smalldatetime).

I am taking the column DateWithTime from table_1 and looking it up with DateAlone from table_2 to get DateSK.

I do not know the right way to lookup date fields. Should I compare day, month and year separately to get DateSK.

Thanks,

Vijay

|||

I commonly use a slight cheat on this one, if you make the integer key of your lookup table the difference in days from 1 Jan 1900 then you can calculate the key instead of looking it up.

You can also use the same trick with the time portion of neccessary (do the diff in seconds).

Hope that helps you

Philip

|||

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

|||

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

|||

Philip Coupar wrote:

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

yyyy--mm-dd is unambiguous.