Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts

Sunday, March 25, 2012

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.

Wednesday, March 21, 2012

Date out of range?!

Greetings!

I have a data source that gets generated based on a variable with the following SQL :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

But when I run my package I get an error message saying:

The conversion of CHAR to DATETIME resulted in a DATETIME value out of range

The Last_Run_Date variable is set to '2006-06-25 14:35:05.450'

When I run the code in a QA session it works, but in the package it complains! What am I doing wrong?

Thanks for your help in advance.

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

If the latter...use SQL Profiler to check what is getting issued.

-Jamie

|||

Does the error get thrown by SSIS eval;uating the expression or by SQL Server when it gets executed?

The expression evaluates correctly. The error occurs when I run the package.

|||

Yeah I know that. I mean when it actually fails, is it the expression evaluation that fails or the issuing of the SQL? Can you post the full error message?

Just cos the expression evaluates successfully at design-time it does not necassarily mean it will do at execution-time.

-Jamie

|||

Hi Jamie,

The error message I get is:

Error: 0xC0202009 at Load Deleted Data, Result on DevTill [1]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "The conversion of CHAR to DATETIME resulted in a DATETIME value out of range.".

Error: 0xC004706B at Load Deleted Data, DTS.Pipeline: "component "Result on DevTill" (1)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Load Deleted Data, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Load Deleted Data: There were errors during task validation.

Warning: 0x80019002 at Route A: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Result Transformation: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Result Transformation Faster Version.dtsx" finished: Failure.

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

-Jamie

|||

Hmmm...at what point does it fail? Is it when the Execute SQL Task executes or is it when the package is getting validated (which is the very first thing that happens - before any tasks have executed.

While the package is being validated.

By the way, the variable User::Last_Run_Date is a datetime variable. In my SQL string (the one used to generate the data source) am casting my datatime variable as a character. But I can't see why that would cause a problem.

|||

Just a guess, but what is in your variable when you deploy the package?

I dare say that this will succeed if you set DelayValidation=TRUE on the Execute SQL Task.

-Jamie

|||

Hi Jamie,

The error does not occur while the SQL Task is being validated. The Task correctly picks the right date and assigns it to my variable. The variable then gets used in a Data source task. It's at the point of validating/running the datasource part of my Data Flow component that I get the error message.

|||

Then there is something wrong in the SQL. Use Profiler to see what is getting issued against SQL Server.

Or, use this technique to get the SQL statement in a watch window: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie

|||

Is it possible that your client - SSIS - is using a different locale from the server? I have seen run into this occasionally as my home PC uses a UK locale.

If so, then you may find that running the query in QA succeeds, but the client fails. But you'll need to work on it quickly - in a few days (1/7, or 7/1 depending on your locale) it may magically look like it is working again.

Donald

|||

Hi Donald,

Yes that is a possibility but how to change the date format in SSIS?!

|||

Donald is referring to the locale if your client workstation on which SSIS is installed.

However, this is a murky area. You should read this post: http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx and be aware of it.

Did you use Profiler to check what was getting issued? Profiler is always the first place to start in these situations - I can't stress just how useful a tool it is.

-Jamie

|||

Hi Jamie,

I am not quite sure how to use profiler for this problem.

I have a data source coming from SQL Server 6.5. The data source is generated from a variable with the following expression :

"select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @.[User::Last_Run_Date] + "'"

The @.[User::Last_Run_Date] is a datetime variable

When I run it, I get the error about not being able to convert CHAR to DATETIME. The value of my Last_Run_Date is 2006-06-25 14:35:05.450

How am I suppose to use profiler for this?!

|||

You've already told me further up this thread that the error is getting thrown by SQL Server when you issue that query from SSIS. You have 2 ways of finding out what query SSIS is issuing after it has evaluated that expression:

1) Use profiler. However this is the first time you have mentioned that it is SQL Server 6.5. I don't actually know if Profiler works for SQL 6.5 though I assume it does.

2) Put the query into a variable and use a watch window

Both of which I've already mentioned in this thread and I gave you a link for option 2.

-Jamie

Monday, March 19, 2012

Date is not getting saved in propper manner

hi friends

In my form i am storing system's date in the database.

4 that i m using a variable of date like:

dim today as new date

today=now.date

but its storing the default date that is '1/1/1900'

how to overcome this problem?

Hello:

Can you show us some of your code using to perform so?

Regards

|||

Hey,

If you are storing the date for when the row was created, you could set a default value for the date field with the default value of getdate(); this will get the date from the system time. Otherwise, try DateTime.Today instead of using a date variable. From that, you can then use ToString() to convert it to a string, or whatever you want to do with it.

Saturday, February 25, 2012

Date format

I'm trying to build a file-name variable that begins with a 'date'.

For example, I need to build logic in a T-SQL script that will access a file called 08032006abc123, with '08032006' being a representation of the date 8/3/2006.

Is there a quick/easy way to create this string, based on the date 8/3/2006? (would be nice if there was a date function that would zero-fill)

If you can use the date format YYYYMMDD or YYMMDD you can use CONVERT to get the encoded date ( http://msdn2.microsoft.com/en-us/library/ms187928.aspx ) else you can always do this and then rearrange the out string using the SUBSTR|||

The following syntax will return the date in the manner you are looking for:

DECLARE @.date datetime

SET @.date = GETDATE()

select replace(convert(varchar(10), @.date, 101), '/', '')

Results: 09082006

Friday, February 17, 2012

date comparison

Is there a way to compare to a date variable with a 'flattened' date? What
I mean is, something like this:
select * from paysched where sdate = '02/24/2003'
now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
works. But what if it were '02/24/2003' 09:22:02'? I could do a 'between',
I know, but is there any way to 'flatten' the sdate in the select so that it
only contains the date, not the time, element?
Tx for any help.
Bernie YaegerWhile you can do something like that, you don't want to. The reason is the
any attempt to manipulate the value of the column in the where clause will
cause the optimizer to ignore any potentially useful indexes on that column.
Your best bet is to use between.
Less efficiently:
where convert(char(8), sdate, 112) = '20030224' -- note - string to string
comparison
where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
string constant promoted to datetime
where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
I'm sure there are others. BTW - you should format your strings as yyyymmdd
if you want them to be convertable to datetime without worrying about
regional or language or dateformat settings.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> Is there a way to compare to a date variable with a 'flattened' date?
What
> I mean is, something like this:
> select * from paysched where sdate = '02/24/2003'
> now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> works. But what if it were '02/24/2003' 09:22:02'? I could do a
'between',
> I know, but is there any way to 'flatten' the sdate in the select so that
it
> only contains the date, not the time, element?
> Tx for any help.
> Bernie Yaeger
>|||Hi Scott,
Tx for the advice. I had actually been working with convert to get it done
while awaiting a response. Re the optimizer - this is a very small table
(maybe 150 rows) so it is not significant here, but I appreciate your
recommendation.
Tx again,
Bernie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is
the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that
column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so
that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Scott,
FYI, in all your three examples you do not have the datetime column "alone" in the WHERE clause.
This will mean that SQL Server cannot use an index for that search argument. My favorite is:
DECLARE @.TheDate
SET @.TheDate = '20030523'
WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
Note that the column is not involved in any type of calculation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Morris" <bogus@.bogus.com> wrote in message news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Exactly - I was giving the less efficient examples. The recommendation was
to use between (same as >= and <=).
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> Scott,
> FYI, in all your three examples you do not have the datetime column
"alone" in the WHERE clause.
> This will mean that SQL Server cannot use an index for that search
argument. My favorite is:
> DECLARE @.TheDate
> SET @.TheDate = '20030523'
> WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> Note that the column is not involved in any type of calculation.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > While you can do something like that, you don't want to. The reason is
the
> > any attempt to manipulate the value of the column in the where clause
will
> > cause the optimizer to ignore any potentially useful indexes on that
column.
> > Your best bet is to use between.
> >
> > Less efficiently:
> > where convert(char(8), sdate, 112) = '20030224' -- note - string to
string
> > comparison
> > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> > string constant promoted to datetime
> > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> >
> > I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> > if you want them to be convertable to datetime without worrying about
> > regional or language or dateformat settings.
> >
> > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > Is there a way to compare to a date variable with a 'flattened' date?
> > What
> > > I mean is, something like this:
> > >
> > > select * from paysched where sdate = '02/24/2003'
> > >
> > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > 'between',
> > > I know, but is there any way to 'flatten' the sdate in the select so
that
> > it
> > > only contains the date, not the time, element?
> > >
> > > Tx for any help.
> > >
> > > Bernie Yaeger
> > >
> > >
> >
> >
>|||left off a bit - between is the same ... assuming the appropriate boundaries
are used.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%2375zDtimDHA.3256@.tk2msftngp13.phx.gbl...
> Exactly - I was giving the less efficient examples. The recommendation
was
> to use between (same as >= and <=).
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> > Scott,
> >
> > FYI, in all your three examples you do not have the datetime column
> "alone" in the WHERE clause.
> > This will mean that SQL Server cannot use an index for that search
> argument. My favorite is:
> >
> > DECLARE @.TheDate
> > SET @.TheDate = '20030523'
> > WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> >
> > Note that the column is not involved in any type of calculation.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott Morris" <bogus@.bogus.com> wrote in message
> news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > > While you can do something like that, you don't want to. The reason
is
> the
> > > any attempt to manipulate the value of the column in the where clause
> will
> > > cause the optimizer to ignore any potentially useful indexes on that
> column.
> > > Your best bet is to use between.
> > >
> > > Less efficiently:
> > > where convert(char(8), sdate, 112) = '20030224' -- note - string to
> string
> > > comparison
> > > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
> note -
> > > string constant promoted to datetime
> > > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> > >
> > > I'm sure there are others. BTW - you should format your strings as
> yyyymmdd
> > > if you want them to be convertable to datetime without worrying about
> > > regional or language or dateformat settings.
> > >
> > > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > > Is there a way to compare to a date variable with a 'flattened'
date?
> > > What
> > > > I mean is, something like this:
> > > >
> > > > select * from paysched where sdate = '02/24/2003'
> > > >
> > > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so
it
> > > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > > 'between',
> > > > I know, but is there any way to 'flatten' the sdate in the select so
> that
> > > it
> > > > only contains the date, not the time, element?
> > > >
> > > > Tx for any help.
> > > >
> > > > Bernie Yaeger
> > > >
> > > >
> > >
> > >
> >
> >
>