How do you combine 3 date fields into one date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How do you combine 3 date fields into one date?

Author

Topic

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-04 : 10:56:09

I've created the sql code below which creates three different date based fields.

I now need to combine the three fields into one date field. It needs to display as DD/MM/YY with the date, month and year being the combined fields below. The combined field must be treated as a date field so that I can use it for calculations later.


DATEPART (yyyy,GetDate()) as 'Current Year',
DATEPART (d,tblClient.YearEnd) as 'Year End Day',
DATEPART (m,tblClient.YearEnd) as 'Year End Month'

Thanks in advance!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-04 : 11:07:59

DECLARE @yearEnd datetime
SET @YearEnd = '20090527'

select dateadd(year,datediff(year,@YearEnd,getdate()),@yearEnd)

You can then format it in the front end

Jim

Everyday I learn something that somebody else already knew

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-04 : 11:16:05

I'm not entirely sure I follow.

DATEPART (yyyy,GetDate()) as 'Current Year', This will currently be 2011
DATEPART (d,tblClient.YearEnd) as 'Year End Day', for example 31
DATEPART (m,tblClient.YearEnd) as 'Year End Month' for example 12

Given the three fields above I need to create a fourth field, which is a date field that combines the three fields.

For the example above it would show 31/12/2011.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-04 : 11:23:34

Does my query produce the wrong date, or just the wrong format? Are you just tryin to get the last day of the current year? Please give an example of what's in tblClient.YearEnd and what you want the output to be.

Jim

Everyday I learn something that somebody else already knew

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-04 : 11:36:04

Jim

tblClient.YearEnd is a datefield and will show for example 31/12/2010, 31/07/2007 etc.

The first line of my query gets the current date, the other two lines get the month and date from the YearEnd field.

Given my query above the output needs to be as follows:

31/12/2011

All I want to find out is how to combine/merge/join the three parts into one date field.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-04 : 11:53:02

SELECT DATEADD(DAY, (d,tblClient.YearEnd), DATEADD(MONTH, (m,tblClient.YearEnd), DATEADD(YEAR, DATEPART (yyyy,GetDate()) - 1900, -1)))

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-04 : 12:01:38

quote:
Originally posted by Lamprey

SELECT DATEADD(DAY, (d,tblClient.YearEnd), DATEADD(MONTH, (m,tblClient.YearEnd), DATEADD(YEAR, DATEPART (yyyy,GetDate()) - 1900, -1)))

that gives me "incorrect syntax near ','

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-04 : 12:08:28

I still don't see what's wrong with my query. What's the output for this date 31/07/2007 ?

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-04 : 15:32:41

Yeah I forgot the DATPART for two of the items.

SELECT DATEADD(DAY, DATEPART (d,tblClient.YearEnd), DATEADD(MONTH, DATEPART (m,tblClient.YearEnd), DATEADD(YEAR, DATEPART (yyyy,GetDate()) - 1900, -1)))

But, Jims's query is more elegant:

select dateadd(year,datediff(year,tblClient.YearEnd,getdate()),tblClient.YearEnd)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-04 : 15:41:43

[code]DECLARE @Sample TABLE
(
YearEnd DATETIME NOT NULL
)

INSERT @Sample
(
YearEnd
)
VALUES ('19991231'), -- Historic year
('20001231'), -- Leap year
('20201231') -- Future year

-- Solution
DECLARE @WantedYear SMALLINT = 2011

SELECT YearEnd,
DATEADD(YEAR, @WantedYear - DATEPART(YEAR, YearEnd), YearEnd) AS Peso
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-07 : 06:44:12

Jim

Your formula does work but it doesn't quite achieve what I need it to.

Let me explain better:

In our database each client has a year end date field. The day and month stay the same but the year of course changes each year.

The date that is currently in the field contains the correct day and month but the year part reflects the year when the date was entered into the field.

For example, a client might have 31-December-2003 in the year end field (the front end just shows 31/12 as this is what is referred to - the year is not referred to as such).

I now need to produce some queries that take into account the year end but they must also reflect the CORRECT year end.

So for example, a client who currently has 31-December-2003 in the year end date field the year should actually be 2010 (the date of the clients last business year end).

My query separates the day and month part of the year end field which is fine. I have created an additional field which simply works out the current year.

I now need to merge the the three separate fields into one date field. i.e. with three separate fields of 31 (day), 12 (month) and 2011 (year) I want to combine the three to get a SINGLE date field of 31-December-2011.

In Crystal reports this is simply a case of creating a formula as follows to combine the fields and set as a Date field:

Date (@Year}, {@Month},{Day})

I need to find out what the SQL equivalent of this formula is.

Hope that makes better sense.

Cheers

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-07 : 07:25:57

Then use
declare @date datetime
set @date = '20030527'
select dateadd(year,datediff(year,'20031231',getdate()),'20031231')
or Peso's. Peso's requires that you input the correct year, while mine assumes that the correct year is always the current one.

Jim

Everyday I learn something that somebody else already knew

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 08:02:01

quote:
Date (@Year}, {@Month},{Day})

I need to find out what the SQL equivalent of this formula is.

There isn't a built in function that works exactly like that DATE(y,m,d) function.

This is one way..

SELECT
Cast(CONVERT(Varchar(8), @Y*10000 + @M*100 + @D) as datetime)

Poor planning on your part does not constitute an emergency on my part.

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-07 : 08:23:58

cheers I'll give that a go.

Out of interest what do the *1000 and *100 mean?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-07 : 08:31:17

He is creating YYYYMMDD. YYYY * 10000 = YYYY0000+ MM*100 = YYYYMM00 + DD = YYYYMMDD. There's no need to convert back and from date to string, though.

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-07 : 08:59:33

quote:
Originally posted by dataguru1971

[quote]There isn't a built in function that works exactly like that DATE(y,m,d) function.

Sort of...
DATEADD(MONTH, 12 * @Year + @Month - 22801, @Day - 1)

N 56°04'39.26"
E 12°55'05.63"

BadBoy House
Starting Member

41 Posts

Posted - 2011-02-07 : 09:50:31

dataguru's code seems to have done the trick.

thanks once again chaps!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 09:52:32

That's handy Peso..thanks! but there isn't one that just accepts the 3 parameters and spit back a date..there is no equivalent built in function... There IS however, built in functionality that can be used to do the same... :)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources