June 24, 2013 at 11:38 pm
Any body Can you please write query for calculating a upcoming birth day in a week?
Thanks in advance
June 25, 2013 at 8:02 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2013 at 8:04 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2013 at 8:14 am
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....
June 25, 2013 at 8:20 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2013 at 8:23 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2013 at 10:27 pm
i have data in this format only and i wanna store one date so that why i have mentioned in int datatype of DOB
June 26, 2013 at 12:46 am
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*
FROMmembers AS m
WHERERIGHT(m.dob,4) BETWEEN @startdate AND @enddate
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 26, 2013 at 7:50 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 10:37 pm
Thank you very much
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply