Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Required Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 11:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
Any body Can you please write query for calculating a upcoming birth day in a week?


Thanks in advance
Post #1466977
Posted Tuesday, June 25, 2013 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1467166
Posted Tuesday, June 25, 2013 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
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/
Post #1467169
Posted Tuesday, June 25, 2013 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
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....
Post #1467180
Posted Tuesday, June 25, 2013 8:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
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/
Post #1467186
Posted Tuesday, June 25, 2013 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1467188
Posted Tuesday, June 25, 2013 10:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
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
Post #1467444
Posted Wednesday, June 26, 2013 12:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
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/
Post #1467468
Posted Wednesday, June 26, 2013 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1467672
Posted Wednesday, June 26, 2013 10:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
Thank you very much
Post #1467953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse