Im trying to get out all peoples names whos birthdays are coming up in the next 2 weeks, can anyone tell me why this :
SELECT FullName
FROM Users
WHERE (CONVERT(datetime, DOBMonth + '/' + DOBDay + '/' + DOBYear) >= DATEADD(dd, -14, GETDATE())) AND (CONVERT(datetime,
DOBMonth + '/' + DOBDay + '/' + DOBYear) <= DATEADD(day, 0, GETDATE()))
returns nothing when I know theres a few birthdays that should be returned. Thanks Si!
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.
I want to retreive users that had their birthdays 7days before today and will have their birthday 7 days from now (so 14 days in total).What would the SQL for such a thing look like?I now have ths, but that doesnt work when you are at the beginning of a month (march 1). So there must be a more clever way :)select firstname from userswhere (month(ud.birthdate)=month(getdate())) and (month(ud.birthdate) between month(getdate())-1 and month(getdate())+1 ) and (day(ud.birthdate) between day(getdate())-7 and day(getdate())+3 )
View 4 Replies View RelatedHello,
I just joined the forum and consider myself a SQL beginner. I have installed and supported, run backups and similar, but haven't done much with queries and scripting.
I have a MS SQL 2000 server with a company database on it. Amongst other things, the database contains names, birthdays and e-mail addresses. How difficult would it be to setup an automated query to run every morning comparing birthdays to the current date and send automated e-mails out to matching people saying "Happy birthday ...."?
Thanks in advance,
MrNobody
"If I'm not there, I must be somewhere else"
I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.
Birthdays sorted by date:
05/10/1979 jim
12/01/1980 bob
10/04/1983 mike
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
05/07/1999 dylan
The order I wish to achieve is:
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
12/01/1980 bob
05/10/1979 jim
05/07/1999 dylan
Thanks in advance.
Mike
hi,
how can I get the first day of the week 12 weeks ago ?
This is one thats got me perplexed...
I need to find out all the weeks in a year,
then for each week work out the numbers of records entered into the table that week that fit a certain criteria,
and then report back the dates involved and the amount of records that were counted! Phew...
Basically I'm trying to find the week in a year(s) that had the most records entered into the table during that week...
And I'm stuck, I've got it working with individual dates but is it possible to group those dates into weeks???
Any help would be appreciated...
Cheers
hey all
i am trying to get a filter in a report that return the last 14 complete weeks. so sunday through to Monday but only display this week once it's comlete next week. i have this
FTDW.dbo.day.sql_date < dateadd(w,-1,getdate()) and FTDW.dbo.day.sql_date > dateadd(w,-15,getdate())
but i get incomplete week 3 and last week. any ideas?
I'm trying to find a simple way to see if a date in a week. I would like to try something like this, but haven't gotten it to work yet:
case when snapshot_date in (datepart(week,getdate())) then 'yes' else 'no' end as week1,
case when snapshot_date in (datepart(week,getdate())-1) then 'yes' else 'no' end as week2
What am I doing wrong and how would I correctly go about this?
SQL Server 2000 SP3Hi,How can I get the cumulative weeks from a givedate to the currentdate. I know I can get the weeknumber by using datepart(wk,getdate())but this will giveme the week number for this year. What if I want to know the number ofweeksthat have passed since june 1 2001. If I use datepart(wk,'20010106') Iwillget the week number for 2001 but I would like the number of weeksexpired between then now.Thanks,Reg
View 1 Replies View Relatedhi
i want to write query in such way that if employee who not worked from last 4 or 6 week will listed...
how we can do this?
thanx
Hi all,
I have a problem with datepart. I want to obtain the week from one date. I have seen the documentation about datepart and the problem with the week counter but I have still problems.
For example.
I want to get the week from this date 29 Dec 2003. This week with DATEFIRST=1 must be the first of 2004 (datepart(ww,'12/29/2003')) but the system give me the 53 week from 2003
the first day of 2004 is on the same week an this function give me different results.
SELECT datepart(ww,'12/29/2003')
SELECT datepart(ww,'1/1/2004')
I don't understand anything ¿?
Thanks a lot.
I should check that the the number weeks of work for the five names is the same for all... that is, there can not be a person who works more or less of other...
I create a table `tbl_dates` where the primary key is the date and another field used for the holiday.
How do distribute equally these five names for the working weeks in the year?
[URL] ....
hi, I am trying to group my data into weeks in a month.
I am using :
GROUP BY DateAdd(day, -1 * datepart(dw, convert(char(10),date1,23)),
convert(char(10),date1,23))
Which works ok, but my first day is the monday of each week.
Do you know how I can group by weeks,
where dates run from monday to sunday ?
So IN march I would have
week begin 3/3
week begin 10/3
week begin 17/3
week begin 24/3
week begin 31/3
But for the last day I only want to count the 31st, not the entire week.
Same goes for the beginning of the month,
eg for april, I want it to show as week beginning 31st march but I only count aprils data.
I hope that makes sense.
can you help ?
All,
I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.
After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.
1) What is a good approach for this since time is not static and it is changing.
any example or piece of sql syntax is greately appreciated.
Thaks in advance,
aa-
Hi, I have a problem with the region settings on SQL Server Compact Edition.
To begin with, in my C# Windows Mobile app, I start with fetching records for the
current month from SQL Server 2005 Express. That works without any problems,
however, when I try to filter these records locally by weeknumber something happends
SQL Server CE uses Sunday as the first day of the week, which is inconvenient
since I live in Europe.
I tried to "SET DATEFIRST 1" in a query prior to the one fetching weekly records
but that just result in an exception.
Does anyone know how to change the regional settings on SQL Server CE?
I have data entered into a table using a datetime field. How can I group the data one week at a time and show mulitple weeks at a time?
View 14 Replies View Relatedi have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"
i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.
weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301
Average calculations Here :
week 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4
Plz lemme know if u have any suggestions..
Aim - Looks at the “CreatedDate” for the latest month and count how many created dates fall within a given week
Currently the created date is displayed as
2013-11-01T09:08:54.000Z
2013-11-05T12:32:26.000Z
2013-11-15T12:37:17.000Z
So for Example
Week 1 = 2
Week 2=1
Week 3=0
Week 4= 0
My table is
select
[CreatedDate],
from #build
I have a db that grew 8.2GB in one week and don't understand why.There are three tables added to the db daily. I calculated thespaceused by each of the three tables for a period of two weeks. Thetatal amount of data added to the db for the three daily tables overthe past two weeks was about 4MB yet the db grew approximately 8.2GB.WHY?Can someone please tell me what I should look at so that I canunderstand what is going on?
View 4 Replies View RelatedMy table is test and I have an ID and DateTest columns
I would like to count the weeks with more then one record.
So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record
select sum(c)
from (
select c = count( id) over (partition by id, datepart(week, DateTest))
from test where id = '1' and DateTest >= '7-7-2015'
) a where c = 1
Hi,
i've this phenomenon that my SQL SERVER 2005 SP2 Build 3042 (W2K3 STD SP2) stop responding to connections.
when i make stop and start to the sql server service it's back to life.
when i observer the windows event log and sql events i see those errors
from windows eventvs
AppDomain 24 (system.dbo[runtime].24) is marked for unload due to memory pressure.
AppDomain 24 (system.dbo[runtime].24) unloaded.
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.165.2.173]
from SQL events
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.165.2.173]
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: <local machine>]
Unsafe assembly 'hebfilts, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 28 (system.dbo[runtime].28).
Plz help me
Thx
I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:
WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5
I require some scripting to return data which sits in a rolling weeks date range of the current week.
For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)
And so forth.
So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.
hi friends,
I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks
i used the below query but itz not working
sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) "
..can anyone suggest me how to retrieve data for current week , for a related item
thanks in advance
i need urgent help.
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
View 7 Replies View RelatedI'm having problems filtering in my MDX query.
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
[Code] ....
The dates are hard-coded for testing purposes.