Date range

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @.Begin Date AND @.EndDate. I want to include all of the @.EndDate.well, BETWEEN is inclusive but your probably not receiving all of the enddates due to the time part of datetime datatype.

Another method is to add 1 day to the enddate (with no time indicator) and use:

WHERE date1 >= @.beginDate AND date1 < @.enddate

|||I used WHERE date1 BETWEEN @.Begin Date AND @.EndDate + '24:59:59' to include the data from the last day of the search.

So I assume there is no benefit from using between to < and >.|||Your query is not correct. It will exclude 1 second before midnight (SQL Server datetimes do include ms)

You should be using:

where Date1 >= @.Begin
 and Date1 < @.End + 1

OR

where convert(datetime, convert(varchar(10), Date1, 111), 111) = @.Begin

if it's only one day|||Using the CONVERT function would make the query non-sargable -- don't do it that way!! Definitely stick with Pierre's first option.

Terri|||Adding one to the date doesn't pose any issues? @.date + 1?|||As long as @.date does not contain any time data (ie it is the default midnight 00:00:00.000) that method should be fine.

Terri

Date diff

I have tow date fields and need to display the days between the two dates.
When I do a direct date1 - date2 I get something in the following format
dd:hh:mm:ss
Is there a way to get it show just the days ?
thanksOn Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have tow date fields and need to display the days between the two dates.
> When I do a direct date1 - date2 I get something in the following format
> dd:hh:mm:ss
> Is there a way to get it show just the days ?
> thanks
If I'm understanding you correctly, this expression should help.
=DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
Regards,
Enrique Martinez
Sr. Software Consultant|||thanks, that worked
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:edd5245d-c6fc-459d-8956-04a63d035e0d@.t1g2000pra.googlegroups.com...
> On Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> I have tow date fields and need to display the days between the two
>> dates.
>> When I do a direct date1 - date2 I get something in the following format
>> dd:hh:mm:ss
>> Is there a way to get it show just the days ?
>> thanks
>
> If I'm understanding you correctly, this expression should help.
> =DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Jan 15, 5:08 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> thanks, that worked"EMartinez" <emartinez...@.gmail.com> wrote in message
> news:edd5245d-c6fc-459d-8956-04a63d035e0d@.t1g2000pra.googlegroups.com...
> > On Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have tow date fields and need to display the days between the two
> >> dates.
> >> When I do a direct date1 - date2 I get something in the following format
> >> dd:hh:mm:ss
> >> Is there a way to get it show just the days ?
> >> thanks
> > If I'm understanding you correctly, this expression should help.
> > =DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Date Conversion

Hi,

Please help me on this conversion. I am using DTS to import data from text file to SQL Server 2000. I have these Date fields in the textfile

Date1 yyyymmdd
Date2 yyyymm

What corresponding data type should I define in SQL server. Datetime and smalldate does not work.

ThanksHowdy

The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.

If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'

Date format in BOL is not documented well, sadly.

So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.

Let me know if I have interpreted your problem correctly.

Cheers,

SG.|||i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)

once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table

e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say

select
cast( left(fieldx,4)
+'-'+substring(fieldx,5,2)
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-'+substring(fieldy,5,2)
+'-01' as datetime ) as fieldydate
, ...
into newtable
from loadedtable

edit: cut & paste typo
rudy
http://r937.com/|||the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.

Hope this helps.