Best way to copy database: records

Showing posts with label records. Show all posts

Tuesday, March 27, 2012

binding a database records to a dropdownlist

Hey I just wanted to know if anyone could tell me how to bind values from a database to a dropdownlist?

DropDownlist.DataSource = dataset; // dataset returned from the database

DropDownlist.DataBind();

|||

Hi here is a detailed code fromdata bind a DropDownList declaratively to a data source control:

<html><head> <script language="VB" runat="server"> Sub SubmitBtn_Click(sender As Object, e As EventArgs) Label1.Text = "You chose: " + DropDown1.SelectedValue End Sub </script></head><body> <h3><font face="Verdana">DataBinding DropDownList</font></h3> <form runat=server> <asp:DropDownList id="DropDown1" DataSourceID="SqlDataSource1" DataTextField="au_lname" DataValueField="au_id" runat="server" /> <asp:SqlDataSource id="SqlDataSource1" ConnectionString='<%$ ConnectionStrings:Pubs%>' SelectCommand="select DISTINCT au_id, au_lname from authors" runat="server"/> <asp:button Text="Submit" OnClick="SubmitBtn_Click" runat=server/> <p> <asp:Label id=Label1 Font-Names="Verdana" font-size="10pt" runat="server" /> </form></body></html>

Hope it helps.

Tuesday, March 20, 2012

bigint field vs int

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int

i need to know about the performance and difference

will it affect the performance much and second will i have to change any code like delete,update

A standard Int ranges from -2,147,483,648 through 2,147,483,647 so you can use them to store values much larger than 100000.

If you do use bigints, you would need to use Int64 in your code

|||

HI,

There will be some performance difference in using int and bigint. If you use bigint the performance is slower in an 32 bit processer. But in case of a 64 bit processor the performance should not be a problem

|||

Int will give you up to 2,147,483,647 while Bigint gives up to 9,223,372,036,854,775,807. The only difference in your stored procedure is that you will need to change the parameter declarations from INT to BIGINT.

>i need to know about the performance and difference
Obviously more working memory will be required for BIGINT instead of INT (8 bytes instead of 4 for for each value), however SQL's will still work very efficiently provided there is enough RAM. If you only have enough RAM for INT, changing to BIGINT will put you at a disadvantage - so make sure you have enough RAM.

sql

Wednesday, March 7, 2012

BETWEEN PROBLEM IN DATETIME QUERY

I have some Records in my table like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-19 14:17:10.000
2006-06-17 14:17:28.000
I want to have count number of the records if they have same date. I dont
care about hour,minute and second info. I care about query result based on
daily records.
When I Wrote
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> @.Date
It returns
13.06.2006 1
15.06.2006 1
16.06.2006 2
17.06.2006 2
19.06.2006 1
it is right one..
When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
instead of > @.Date )
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
BETWEEN @.Date AND @.Date2
It olny returns
13.06.2006 1
What is the mistake ?
Could anyone help me ?Why are you converting in your having/where clause?
Take a look at this
create table TBL_referer (Referer_time datetime)
insert TBL_referer values('2006-06-16 14:15:01.607 ')
insert TBL_referer values('2006-06-16 14:17:28.187')
insert TBL_referer values('2006-06-15 14:17:28.000')
insert TBL_referer values('2006-06-13 14:17:28.000 ')
insert TBL_referer values('2006-06-17 14:17:28.000')
insert TBL_referer values('2006-06-19 14:17:10.000')
insert TBL_referer values('2006-06-17 14:17:28.000')
DECLARE @.Date AS datetime
DECLARE @.Date2 AS datetime
SELECT @.Date='19000613'
SELECT @.Date2='20100613'
-- >
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time > @.Date
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
--Between
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time BETWEEN @.Date AND @.Date2
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
You see no HAVING but a WHERE clause and no convert either
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
> care about hour,minute and second info. I care about query result based on
> daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
> instead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?|||Thank You For your helpinh But How come Sql server evaluates
That.
WHERE Referer_time BETWEEN @.Date AND @.Date2
I Have
SELECT @.Date='19000613'
SELECT @.Date2='20060618'
But my records are like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-13 14:17:10.000
2006-06-17 14:17:28.000
can you give me an article or explanation how it does it ?|||Here is all you need
http://www.karaszi.com/SQLServer/info_datetime.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Thank You For your helpinh But How come Sql server evaluates
> That.
> WHERE Referer_time BETWEEN @.Date AND @.Date2
> I Have
> SELECT @.Date='19000613'
> SELECT @.Date2='20060618'
> But my records are like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-13 14:17:10.000
> 2006-06-17 14:17:28.000
> can you give me an article or explanation how it does it ?|||This should help you: http://www.karaszi.com/SQLServer/info_datetime.asp. Yo
u can post back if you
still have problems after reading the article.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <in da club> wrote in message news:OE457ZdlGHA.4052@.TK2MSFTNGP05.phx.gbl...[co
lor=darkred]
>I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
care about hour,minute
> and second info. I care about query result based on daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2 i
nstead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?
>
>
>[/color]

BETWEEN keyword

I need to retrieve records where the date is in between the current date and 4 days previous.

I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...

Can someone help out?You are probably having problems because both the date and the time are used in such comparisons.

See if this post helps you:view post 483129

Terri|||Ok, thanks Terri.

Between date problem

I have a SP that gets two dates passed to it. Those dates are then used to
pull records created between those dates. The problem is that the created
date is a timestamp and so when I pass 3/15/2006 and 3/15/2006 no rows are
returned. If I want today's records I have to pass 3/15/2006 and 3/16/2006.
This is my where clause WHERE CONVERT(VARCHAR(10),Created_TS,112) BETWEEN
@.strFromDate AND @.strToDate How can I fix this? Thanks.Don't use BETWEEN. And don't pass in a string in that format, use YYYYMMDD
and SMALLDATETIME parameters. Don't do any string conversions of the value
in the table. This will basically destroy performance and doesn't do a
whole lot for readability of the query, either. Finally, pass in the day
*after* the end of the range. So, for 3/15 -> 3/15, pass in 3/15 and 3/16.
Then, say:
CREATE PROCEDURE dbo.foo
@.rangeStart SMALLDATETIME,
@.rangeEnd SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON;
SELECT ...
WHERE Created_TS >= @.rangeStart
AND Created_TS < @.rangeEnd;
END
GO
e.g.
EXEC dbo.foo @.rangeStart = '20060315', @.rangeEnd = '20060316';
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:2A9229F5-B2D5-4830-99D1-2C57FAF75D3C@.microsoft.com...
>I have a SP that gets two dates passed to it. Those dates are then used to
> pull records created between those dates. The problem is that the created
> date is a timestamp and so when I pass 3/15/2006 and 3/15/2006 no rows are
> returned. If I want today's records I have to pass 3/15/2006 and
> 3/16/2006.
> This is my where clause WHERE CONVERT(VARCHAR(10),Created_TS,112) BETWEEN
> @.strFromDate AND @.strToDate How can I fix this? Thanks.

Saturday, February 25, 2012

BETWEEN clause & <= operators

Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.

HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --

where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th

using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>

BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.

Hello,

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted. 23/05/2007 00:00:00

I tried the following

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:

Code Snippet

declare @.dt datetime

set @.dt = 20/5/2007

select @.dt

You could rewrite second query with explicit convert:

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)

Or use

Code Snippet

SET DATEFORMAT dmy

for setting default dateformat

|||That is because the implicit conversion thinks the date is a US date.

Do this:

.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)

SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||

steve_1_rm,

Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.

SELECT *

FROM Calls

WHERE

clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN '20070527' AND '20070530'

AMB

|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||

What do you get when you issue

Code Snippet

SELECT CONVERT(varchar(10), [Date], 103) as [Date], *

FROM Calls

WHERE [Date] BETWEEN '20070521' AND '20070531'

Better method to count records in Custom Paging for SQL Server 2005

heres my problem, since I migrated to SQL-Server 2005, I was able to use theRow_Number() Over Method to make my Custom Paging Stored Procedure better. But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure. What I want to know is: Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction? heres my stored procedure:

SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum,morerecords = (Select Count(Ad_Id) From Ads) FROM Ads) as test
WHERE RowNum Between 11 AND 20

The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me. Thank for your time.

Well, since you want to join a single value (the row count) of a table with other columns from the table, the single value must be returned as a result set from a subquery or a join table. If you don't like using count(Ad_Id) to get the row count, you can join the sysindexes table to get the row count for a specific table. For example:

SELECT RowNum, morerecords, Ad_Id,RowCnt
FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum
FROM Ads) as test,sysindexes s
WHERE RowNum Between 11 AND 20
and s.id=object_id('Ads')
and s.indid=(select min(indid)
from sysindexes where id=object_id('Ads'))

If you have a cluster index on the table, you can replace the green part with 1.

Friday, February 24, 2012

Best Way to Synch 2 Tables?

All,

I have an Access DB. On a nightly basis, I want to look at an Other
DB (not Access, but SQL) and:

+ Add any new records from Other.Clients into Access.Clients

Quote:

Originally Posted by

Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.

Is this something I should use a "tool" (SQL Data Compare, SqlSync,
etc.) to do, or could I pull this off reliably every night just using
SQL from the query I could construct to do the job from inside my
Access DB?

And for question Deux, if I can just use the SQL I will write, what's
the best way to kick off the process at 3:00 am every morning?

Thanks,

PatrickPatrick A (parkins@.stradley.com) writes:

Quote:

Originally Posted by

And for question Deux, if I can just use the SQL I will write, what's
the best way to kick off the process at 3:00 am every morning?

A scheduled job in SQL Server Agent.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.

Sunday, February 19, 2012

Best way to search for all records (Using a Case Statement in a SP)

I have a form with a dropdown or combo box, the user can select <All>
or pick a user name. If they pick a user name my where clause works
fine, buts what's the best way to write "Select All" if they choose
the <All
This is what I have so far, but I don't think I should be using the
LIKE operator.

WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
@.myqarep END
and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
@.myoffice END

thanks for your help!!On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:

>I have a form with a dropdown or combo box, the user can select <All>
>or pick a user name. If they pick a user name my where clause works
>fine, buts what's the best way to write "Select All" if they choose
>the <All>
>This is what I have so far, but I don't think I should be using the
>LIKE operator.
>WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
>@.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
>thanks for your help!!

Hi Paul,

You could use this:

WHERE tblCase.qarep = CASE @.myqrep
WHEN '<All>' THEN tblCase.qarep
ELSE @.myqrep
END
AND tblOffice.officecode = CASE @.myoffice
WHEN -1 THEN tblOffice.officecode
ELSE @.myoffice
END

An alternative that's a bit more work to create but that will probably
perform better is to write different versions of the query; use IF to
select which search arguments are set to All and which are set to a value
and then execute the correct version of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||PaulMac (paulmac106@.hotmail.com) writes:
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END

Since it's a combo, LIKE appears to be a bit of overkill. The normal
procedure is to pass NULL when you want all:

WHERE (qarep = @.myqarep OR @.myqarep IS NULL)
AND (officecode = @.myoffice OR @.myoffice IS NULL)

But of course this works too:

WHERE (qarep = @.myqarep OR @.myqarep = '<All>')
AND (officecode = @.myoffice OR @.myoffice = -1)

At least as long as you don't localize the string...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I will not suggest putting a CASE command in the WHERE clause, you may
just want to use "if" control statement to separate two code blocks
which will be more efficient at database level.

IF Boolean_expression
{ sql_statement | statement_block }
[
ELSE
{ sql_statement | statement_block } ]

paulmac106@.hotmail.com (PaulMac) wrote in message news:<1ee8a467.0409130947.4f608eba@.posting.google.com>...
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
> thanks for your help!!|||Hi Hugo

That worked perfectly...Thank You!!

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<3e6ck0dsnb7m8pvq95ps7ceu2nu0lj353t@.4ax.com>...
> On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:
> >I have a form with a dropdown or combo box, the user can select <All>
> >or pick a user name. If they pick a user name my where clause works
> >fine, buts what's the best way to write "Select All" if they choose
> >the <All>
> >This is what I have so far, but I don't think I should be using the
> >LIKE operator.
> >WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> >@.myqarep END
> > and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> > @.myoffice END
> >thanks for your help!!
> Hi Paul,
> You could use this:
> WHERE tblCase.qarep = CASE @.myqrep
> WHEN '<All>' THEN tblCase.qarep
> ELSE @.myqrep
> END
> AND tblOffice.officecode = CASE @.myoffice
> WHEN -1 THEN tblOffice.officecode
> ELSE @.myoffice
> END
> An alternative that's a bit more work to create but that will probably
> perform better is to write different versions of the query; use IF to
> select which search arguments are set to All and which are set to a value
> and then execute the correct version of the query.
> Best, Hugo

Best way to search a table based on another table's data?

I have a table of keywords (hundreds/thousands of records):

KeywordID KeywordName
--
1 Apple
2 Orange
3 Pear

I then have a table of products (also hundreds/thousands of records):

ProductID ProductName ProductDescription
--
123 Apple Tree Better than an orange tree, this...
124 Great Scent This great scent smells like orange...

What's the most efficent way to search the entire product table and return all records that have any of the keywords from the keyword table (in eiter productname or peoductdescription)?

Thanks,
Ron

Here is one way... I used a join with a like. This will give you one row per matching keyword:

Code Snippet

create table keyword
(
KeywordId int primary key,
KeywordName varchar(10)
)
insert into keyword
select 1,'Apple'
union all
select 2,'Orange'
union all
select 3,'Pear'
go

create table product
(
ProductId int primary key,
ProductName varchar(20),
ProductDescription varchar(40)
)
insert into product
select 123,'Apple Tree','Better than an orange tree, this...'
union all
select 124,'Great Scent','This great scent smells like orange...'
go

select Keyword.KeywordName, Product.ProductName, Product.ProductDescription

from Product
join Keyword
on Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%'

Which returns:

Code Snippet

KeywordName ProductName ProductDescription

-- -- -

Apple Apple Tree Better than an orange tree, this...

Orange Apple Tree Better than an orange tree, this...

Orange Great Scent This great scent smells like orange...

Or if you don't want duplicates, you could use:

Code Snippet

select Product.ProductName, Product.ProductDescription
from Product
where exists (select *
from keyword
where Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%')

Another possibility is to use full text search...|||select p.*
from keywords k inner join products p
on p.ProductName like '%' + k.KeywordName + '%'
or p.ProductDescription like '%' + k.KeywordName + '%'

Best way to search

I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>

Best way to return records in a date range using where clause?

Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?

As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function.

SELECT DateField1
WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102))

Any help is greatly appreciated... btw I'm using SQL 2005.

You can use the BETWEEN syntax and also make use of the function GETDATE (returns the current date) and DATEADD (that ... adds days/months/years/time to a date experssion)

SELECT *FROM YourTableWHERE DateFieldBETWEENGETDATE()ANDDATEADD(m, 6,GETDATE())

Tuesday, February 14, 2012

Best way to import ASCII tab delimited file every week (5,000 records)...

I need to import an ASCII tab delimited file that has roughly 5,000 records
once a week into a SQL Server table. I have researched BCP and it seems
like the way to go. Am I headed in the right direction?

Thanks in advance,

James<JFOREMAN10@.houston.rr.com> wrote in message
news:Xlstb.75400$KY1.58889@.twister.austin.rr.com.. .
> I need to import an ASCII tab delimited file that has roughly 5,000
records
> once a week into a SQL Server table. I have researched BCP and it seems
> like the way to go. Am I headed in the right direction?

It is certainly one way to go. The other is to set up a DTS job. I'd
probably use that since it's the more "modern" way of doing it from within
the DB.

>
> Thanks in advance,
>
> James|||Another option is write a stored procedure that uses Bulk Insert. And
schedule the stored procedure as a SQL Agent Job. (BCP runs on the
command line. So you can also write a stored procedure that uses
xp_commandshell to pass to BCP). - Louis|||xp_cmdshell (I guess beer does make you lose brain cells)

Monday, February 13, 2012

best way to get queries fast

i have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.

thanx in advance

cheers

Hi,

It depends on type of database usage you have, if its an OLTP system then you can not use a lot of indexes as it will slow down insertion and updation, however in OLAP system you can use indexes wisely and this will help a lot.

Sunday, February 12, 2012

Best Way to find the difference between two dates

Hi,

Can some one tell me the best way to find the difference between two dates. I got a table with dates. I need to filter records with dates older than 3 years from today. This calculation should take leap years in to account as well.

Many Thanks

Regards,

-VJ

use the datediff...

Datediff(YY,YourColumn,Getdate()) >3

Sample,

Code Snippet

Create Table #data (

[dates] Varchar(100)

);

Insert Into #data Values('1/1/2000');

Insert Into #data Values('1/1/2005');

Insert Into #data Values('12/4/1999');

Insert Into #data Values('12/3/2006');

Select

from

#Data

Where

datediff(yy, dates, getdate()) > 3

Tuesday, March 27, 2012

binding a database records to a dropdownlist

Tuesday, March 20, 2012

bigint field vs int

Wednesday, March 7, 2012

BETWEEN PROBLEM IN DATETIME QUERY

BETWEEN keyword

Between date problem

Saturday, February 25, 2012

BETWEEN clause & <= operators

Between Clause

Between Clause

Between Clause

BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.

Better method to count records in Custom Paging for SQL Server 2005

Friday, February 24, 2012

Best Way to Synch 2 Tables?

best way to store simple up or down values

Sunday, February 19, 2012

Best way to search for all records (Using a Case Statement in a SP)

Best way to search a table based on another table's data?

Best way to search

Best way to return records in a date range using where clause?

Tuesday, February 14, 2012

Best way to import ASCII tab delimited file every week (5,000 records)...

Monday, February 13, 2012

best way to get queries fast

Sunday, February 12, 2012

Best Way to find the difference between two dates

Best way to copy database

Blog Archive

About Me