Hi just imported a bunch of fields from a text file and now have a bunch of little squares iat the end of each field, I assume that they are linebreaks or carriage returns, what is the easiest way to get rid of these?
D--You can use the replace command.
Eg.
declare @.string varchar(100)
set @.string='ABD'
select replace(@.string,'D','C')
Play around with it and you know how to use it for your problem.|||Originally posted by Patrick Chua
You can use the replace command.
Eg.
declare @.string varchar(100)
set @.string='ABD'
select replace(@.string,'D','C')
Play around with it and you know how to use it for your problem.
You should use it in an Update statement:
UPDATE <TableName> Set <FieldName> =
replace(<FieldName>, CHAR(x), '')
x is the ASCII code to identify your quares; try 10 (= CR), or 13 (= LF)|||worked like a charm thanks char(13) was itsql
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Friday, March 23, 2012
Little discussion about XML field performance
Hello!
I'm starting a study about SQL SERVER 2005. In my job, we need store
millions of data in a database. In this data, have a XML file that are store
d
in a XML field.
The question is: How will be database performance? We have this doubts
because will be data a lot! The average is 10 millions of data add for month
in same table.
Somebody have a cases of this subject?
Sorry my english! :o)
Tks!
Luiz
Procergs - Porto Alegre - BrazilNice Monday. It's raining in Seoul.
This article may help.
http://msdn.microsoft.com/library/d...>
h_SQLXML.asp
Save XML in database, but don't query it as far as possible. That's my
private opinion.
Pohwan Han. Seoul. Have a nice day.
"Luiz Carlos" <LuizCarlos@.discussions.microsoft.com> wrote in message
news:602B8FB8-3238-440A-BE4D-FB6DDFB851D3@.microsoft.com...
> Hello!
> I'm starting a study about SQL SERVER 2005. In my job, we need store
> millions of data in a database. In this data, have a XML file that are
> stored
> in a XML field.
> The question is: How will be database performance? We have this doubts
> because will be data a lot! The average is 10 millions of data add for
> month
> in same table.
> Somebody have a cases of this subject?
> Sorry my english! :o)
> Tks!
> Luiz
> Procergs - Porto Alegre - Brazil
I'm starting a study about SQL SERVER 2005. In my job, we need store
millions of data in a database. In this data, have a XML file that are store
d
in a XML field.
The question is: How will be database performance? We have this doubts
because will be data a lot! The average is 10 millions of data add for month
in same table.
Somebody have a cases of this subject?
Sorry my english! :o)
Tks!
Luiz
Procergs - Porto Alegre - BrazilNice Monday. It's raining in Seoul.
This article may help.
http://msdn.microsoft.com/library/d...>
h_SQLXML.asp
Save XML in database, but don't query it as far as possible. That's my
private opinion.
Pohwan Han. Seoul. Have a nice day.
"Luiz Carlos" <LuizCarlos@.discussions.microsoft.com> wrote in message
news:602B8FB8-3238-440A-BE4D-FB6DDFB851D3@.microsoft.com...
> Hello!
> I'm starting a study about SQL SERVER 2005. In my job, we need store
> millions of data in a database. In this data, have a XML file that are
> stored
> in a XML field.
> The question is: How will be database performance? We have this doubts
> because will be data a lot! The average is 10 millions of data add for
> month
> in same table.
> Somebody have a cases of this subject?
> Sorry my english! :o)
> Tks!
> Luiz
> Procergs - Porto Alegre - Brazil
Wednesday, March 21, 2012
Listbox that show a sql server field content
I would like 2 things:
First i would like to show in a listbox in my web page the content of a sql field.
Besides I would like depending on which value was selected in the first listbox that the second listbox show a definied content.
Thank U very muchHowdy
Try www.aspfree.com
Cheers
SG
First i would like to show in a listbox in my web page the content of a sql field.
Besides I would like depending on which value was selected in the first listbox that the second listbox show a definied content.
Thank U very muchHowdy
Try www.aspfree.com
Cheers
SG
Monday, March 19, 2012
List up-coming birthdays
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !
that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!
Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.
Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function
CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin
return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)
very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail
or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30
This may not work at the end of the year. It will skip the people born on January.|||your right
he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right
he should test on the month and
add an OR for a test on the next year
I guess this should work. BETWEEN is the best way to go
DOB = The field Name
BDAYS = The table name
====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates
The DateDiff function would return minus values also.
By the way Toronto is nice city. I used live for a year and half. I love that city
Originally posted by r937
of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong
i withdraw my solutions
not enough coffee, i guess
i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax
see http://www.dbforums.com/showthread.php?threadid=659590
let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant
it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works
that formula is fabulous
did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.
I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub
it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.
That way I'll be able to find it if I ever need it!
Cheers!|||thanks, i will
:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman
or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!
Blindman could you help me out plz?
I have applied your coding as:
Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
I get an error message saying,
Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.
What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.
-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it?|||Originally posted by hali99
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.
If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.
-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)
i have tested it and have confirmed that it's broken
create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000
it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on
create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')
declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'
select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates
drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.
Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !
that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!
Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.
Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function
CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin
return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)
very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail
or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30
This may not work at the end of the year. It will skip the people born on January.|||your right
he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right
he should test on the month and
add an OR for a test on the next year
I guess this should work. BETWEEN is the best way to go
DOB = The field Name
BDAYS = The table name
====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates
The DateDiff function would return minus values also.
By the way Toronto is nice city. I used live for a year and half. I love that city
Originally posted by r937
of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong
i withdraw my solutions
not enough coffee, i guess
i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax
see http://www.dbforums.com/showthread.php?threadid=659590
let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant
it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works
that formula is fabulous
did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.
I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub
it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.
That way I'll be able to find it if I ever need it!
Cheers!|||thanks, i will
:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman
or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!
Blindman could you help me out plz?
I have applied your coding as:
Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
I get an error message saying,
Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.
What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.
-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it?|||Originally posted by hali99
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.
If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.
-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)
i have tested it and have confirmed that it's broken
create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000
it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on
create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')
declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'
select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates
drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.
List Unused Entries
I am using SQLServer 2000. I have one table (Members) which includes a
MemberID (int) field. I have another table (EventRegs) with a unique
EventID(int) and a foreign key for MemberID. A given MemberID can occur in
more than one row in the EventRegs table. I want to find out which MemberID
values that exist in the Members table that do not occur in any of the rows
in the EventRegs table. Basically, which members are not attending any
events.
I always get tangled up when I try to do this type of query! Can anyone give
me an example to work from?
WayneWayene
SELECT MemberID FROM Members WHERE NOT EXISTS
(SELECT * FROM EventRegs WHERE EventRegs.EventID=Members.MemberID )
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I am using SQLServer 2000. I have one table (Members) which includes a
>MemberID (int) field. I have another table (EventRegs) with a unique
>EventID(int) and a foreign key for MemberID. A given MemberID can occur in
>more than one row in the EventRegs table. I want to find out which MemberID
>values that exist in the Members table that do not occur in any of the rows
>in the EventRegs table. Basically, which members are not attending any
>events.
> I always get tangled up when I try to do this type of query! Can anyone
> give me an example to work from?
> Wayne
>
MemberID (int) field. I have another table (EventRegs) with a unique
EventID(int) and a foreign key for MemberID. A given MemberID can occur in
more than one row in the EventRegs table. I want to find out which MemberID
values that exist in the Members table that do not occur in any of the rows
in the EventRegs table. Basically, which members are not attending any
events.
I always get tangled up when I try to do this type of query! Can anyone give
me an example to work from?
WayneWayene
SELECT MemberID FROM Members WHERE NOT EXISTS
(SELECT * FROM EventRegs WHERE EventRegs.EventID=Members.MemberID )
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:Oz30z0MzFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I am using SQLServer 2000. I have one table (Members) which includes a
>MemberID (int) field. I have another table (EventRegs) with a unique
>EventID(int) and a foreign key for MemberID. A given MemberID can occur in
>more than one row in the EventRegs table. I want to find out which MemberID
>values that exist in the Members table that do not occur in any of the rows
>in the EventRegs table. Basically, which members are not attending any
>events.
> I always get tangled up when I try to do this type of query! Can anyone
> give me an example to work from?
> Wayne
>
Monday, March 12, 2012
List of values that make SQL Blow
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.Not sure... but I recently learnt that .NET doesn't like column names starting with a numeric character so worth bearing in mind if that is your FE.|||I am not sure such a list exists, as technically SQL Server will accept anything between brackets, so long as the name is unique within the table.
create table test1
([(ticks, #, :, -,)] int, -- Given example
[~`!@.#$%^&*()_+] int) -- Top row of the keyboard.
select *
from test1|||I would be tempted to accept alpha numerics only myself just to be sure (but I am a bit of pessimist with these sorts of things). There is also the "reserved words" issue.
Actually - I imagine square brackets would need to be escaped (?).|||Sounds like dynamic creation to me...I run herd over anything getting created in oneof my databases|||Looks like only the close bracket needs to be escaped with an extra close bracket.
drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)
select *
from test1
Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.|||Looks like only the close bracket needs to be escaped with an extra close bracket.
drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)
select *
from test1
Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.
I'm old school myself, try and avoid special chars of any type, except _ in database object nams.|||I thank you all for your input.
I have fought tooth and nail My boss has said to come up with a solution. Mangers can be so OBTUSE
I have determined that the business can add alpha numeric with _
starting alpha ending alpha numeric.
Users will create these in a table that will define relationship then a process will auto generate a change control that will be added to physical structure of the database by the DBA team.
No dynamic running of an alter statment.|||Horrible.
You should rename this thread "List of managers that make SQL blow."|||I awlways wanted a list that would make my girlfr...umm never mind|||OMG Bret, if you're having trouble when she's a girlfriend...
then you are doomed after marriage...then you can get her one of those custom-lettered necklaces that say "abandon all hope, ye who..." ;)
(sorry, couldn't resist)|||Yes Brett. Listen to the advice of Mr. Romance.|||Yes Brett, listen to the advice from Mr Charm about listening to the advice from Mr Romance ;)|||Ouch! :shocked:|||i'm not sure how any of this is relavant to sql server??|||That's because you aren't familiar with the hidden intricacies of the database engines. It's actually pretty sexy in there.
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.Not sure... but I recently learnt that .NET doesn't like column names starting with a numeric character so worth bearing in mind if that is your FE.|||I am not sure such a list exists, as technically SQL Server will accept anything between brackets, so long as the name is unique within the table.
create table test1
([(ticks, #, :, -,)] int, -- Given example
[~`!@.#$%^&*()_+] int) -- Top row of the keyboard.
select *
from test1|||I would be tempted to accept alpha numerics only myself just to be sure (but I am a bit of pessimist with these sorts of things). There is also the "reserved words" issue.
Actually - I imagine square brackets would need to be escaped (?).|||Sounds like dynamic creation to me...I run herd over anything getting created in oneof my databases|||Looks like only the close bracket needs to be escaped with an extra close bracket.
drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)
select *
from test1
Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.|||Looks like only the close bracket needs to be escaped with an extra close bracket.
drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)
select *
from test1
Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.
I'm old school myself, try and avoid special chars of any type, except _ in database object nams.|||I thank you all for your input.
I have fought tooth and nail My boss has said to come up with a solution. Mangers can be so OBTUSE
I have determined that the business can add alpha numeric with _
starting alpha ending alpha numeric.
Users will create these in a table that will define relationship then a process will auto generate a change control that will be added to physical structure of the database by the DBA team.
No dynamic running of an alter statment.|||Horrible.
You should rename this thread "List of managers that make SQL blow."|||I awlways wanted a list that would make my girlfr...umm never mind|||OMG Bret, if you're having trouble when she's a girlfriend...
then you are doomed after marriage...then you can get her one of those custom-lettered necklaces that say "abandon all hope, ye who..." ;)
(sorry, couldn't resist)|||Yes Brett. Listen to the advice of Mr. Romance.|||Yes Brett, listen to the advice from Mr Charm about listening to the advice from Mr Romance ;)|||Ouch! :shocked:|||i'm not sure how any of this is relavant to sql server??|||That's because you aren't familiar with the hidden intricacies of the database engines. It's actually pretty sexy in there.
Friday, February 24, 2012
List Header on every page
Hi gurus
I have a detail list and group is made on State field what i want to do is to repeat state name on every page. I cant use table i have to do it with with the list control. i have tried placing state name in page header by referencing the field in list but it doesnt show up if group spans multiple pages.
In short how could i repeat list group element on every page. I know in tables group headers have property Repeat On New Page but its not the case with list control. guys please help me out.
Thanx in advance
Quick reply is appreciated
Regards Saud
I just saw this post on Chris Hays's weblog and I think it may describe what you are looking for:http://blogs.msdn.com/chrishays/archive/2006/09/27/ContinuedHeader.aspx
- Eva
Subscribe to:
Posts (Atom)