Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Required


Query Required

Author
Message
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
Any body Can you please write query for calculating a upcoming birth day in a week?


Thanks in advance
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
techmarimuthu (6/24/2013)
Any body Can you please write query for calculating a upcoming birth day in a week?


Thanks in advance


Hi and welcome to the forums. You are going to have to provide some level of detail for us to be able to help. There is nowhere near enough information in your post for anybody to have any chance at helping you.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
This doesn't look difficult and it would be good if you try this yourself
If you are stuck somewhere, we would like to see what you have tried and where exactly you are stuck
We can then guide you for the way forward


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
create table members
(
id int identity,
firstname varchar(30),
lastname varchar(40),
dob int
)

insert into members(firstname,lastname,dob) values ('Michel','David',19890630)
insert into members(firstname,lastname,dob) values ('Raja','Kumar',19900625)
insert into members(firstname,lastname,dob) values ('Rahul','Sundar',19910501)
insert into members(firstname,lastname,dob) values ('Arun','Edward',19901219)

i wanna result like dob in 25 june to 31 june of this year....
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
techmarimuthu (6/25/2013)
create table members
(
id int identity,
firstname varchar(30),
lastname varchar(40),
dob int
)

insert into members(firstname,lastname,dob) values ('Michel','David',19890630)
insert into members(firstname,lastname,dob) values ('Raja','Kumar',19900625)
insert into members(firstname,lastname,dob) values ('Rahul','Sundar',19910501)
insert into members(firstname,lastname,dob) values ('Arun','Edward',19901219)

i wanna result like dob in 25 june to 31 june of this year....


Why is the data type of the column dob INT? Any particular reason.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
techmarimuthu (6/25/2013)
create table members
(
id int identity,
firstname varchar(30),
lastname varchar(40),
dob int
)

insert into members(firstname,lastname,dob) values ('Michel','David',19890630)
insert into members(firstname,lastname,dob) values ('Raja','Kumar',19900625)
insert into members(firstname,lastname,dob) values ('Rahul','Sundar',19910501)
insert into members(firstname,lastname,dob) values ('Arun','Edward',19901219)

i wanna result like dob in 25 june to 31 june of this year....


The first thing you should do is to not store dates as integers. Store them as datetime. You are going to have to convert your int to a datetime first to ever use it anyway and validation is painless using a datetime datatype. Make it easy on yourself and store your data in the proper datatype.


create table #members
(
id int identity,
firstname varchar(30),
lastname varchar(40),
dob datetime
)


insert into #members(firstname,lastname,dob) values ('Michel','David','19890630')
insert into #members(firstname,lastname,dob) values ('Raja','Kumar','19900625')
insert into #members(firstname,lastname,dob) values ('Rahul','Sundar','19910501')
insert into #members(firstname,lastname,dob) values ('Arun','Edward','19901219')

select * from #members

drop table #members



So now we have a table with birthdates stored as datetime. What have you tried?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
i have data in this format only and i wanna store one date so that why i have mentioned in int datatype of DOB
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4995
techmarimuthu (6/25/2013)
i have data in this format only

Is there no way you can change it or make somebody else change it?

techmarimuthu (6/25/2013)
i wanna store one date so that why i have mentioned in int datatype of DOB

If this is some sort of justification, I don't understand what you mean here.
But, whatever be the reason it is never a good idea to use an INT datatype to store dates.

If you use an INT data type to store dates, you might face some issues which will be really hard to solve afterwards.
I have listed a few below
1.You will not be able to stop anybody from entering invalid dates ( like 20100230 or 20100431 ) unless you implement some sort of constraints or triggers
2.You cannot be sure that all dates entered are in a specific format. We have had so many questions on forums where people were scratching their heads after finding that a date column defined as VARCHAR has dates in all sorts of formats and they were not able to identify if 20100501 is 01st May or 05th January.

Anyways, the below query should help you get the desired results
DECLARE   @startdate INT
DECLARE @enddate INT

SET @startdate = RIGHT(CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112),4)
SET @enddate = RIGHT(CONVERT(VARCHAR(8),DATEADD(DAY,6,CURRENT_TIMESTAMP),112),4)

SELECT *
FROM members AS m
WHERE RIGHT(m.dob,4) BETWEEN @startdate AND @enddate




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
To add another reason for not storing dates as integers, doing any kind of date math means your queries are going to be slower. Just look at the simple example of finding who has a birthday this month. You have to add a cast(dob as datetime). This means that sql must look at every single row of the table. What happens when you have a million rows? We have datatypes for a number of reasons, they are not there to be a hindrance to your work. Use the proper datatypes for the data you are storing and you will find a lot of things in sql become much easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
Thank you very much
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search