what will be the value of YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()))

Question

what will be the value of YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()))

deeep

SSC Eights!

Points: 950
More actions
December 23, 2008 at 11:18 pm

#216866

hi i am
writing an expression for Dervied column in SSIS
ie
YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE())
to get the Value of Current Date in YYYMMDD format
my Derived column is in the Integer format ,,
so will this expression will be enough to get the current date in YYYMMDD format ..
or this exprression will give me the sum of (YEAR+MON+DAY) as my derived column type is Int ...
Please help,

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

You can definately test that in ur query window.

YEAR(), MONTH() and DAY() functions return integer, so if u're adding them u'll definately get a sum of these three integers.

I'm not sure what u mean by YYY. Either we get YY or YYYY

to get a date in YYYYMMDD format you can convert the date with 112 format.

select convert(varchar, getdate(), 112) for YYYYMMDD

OR

select convert(varchar, getdate(), 12) for YYMMDD

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

deepak.spiral (12/23/2008)
my Derived column is in the Integer format ,,
so will this expression will be enough to get the current date in YYYMMDD format ..
or this exprression will give me the sum of (YEAR+MON+DAY) as my derived column type is Int ...

If your target column is INT, U can write something like this...

declare @a int

set @a=cast(convert(varchar, getdate(), 112) as int) -- Explicity converting the result to int

print @a

declare @a int

set @a=convert(varchar, getdate(), 112) as int -- Implicit conversion to INT...

print @a

Pradeep Singh

deeep SSC Eights! Points: 950 More actions · Answer 3

thanks a lot for ur answers

this Select query Statement doest work within Derived column Expression,

so i have used same query to get the date in the same(YYYYMMDD) format and stored into a variable and using the same..for my Derived cloumns Exprression.

Thanks Again

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.

The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 5

Jeff Moden (12/29/2008)
Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.
The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

:hehe::hehe::hehe: Jeff, interesting description "Death by SQL" - it can be occur sometimes 😛

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ps. One Orange Chip Points: 29422 More actions · Answer 6

Hmmm.. there was a silly copy/paste error in the following line :w00t:..

set @a=convert(varchar, getdate(), 112) as int -- Implicit conversion to INT...

You need to remove the part mentioned in bold from the above line.....

Pradeep Singh

deeep SSC Eights! Points: 950 More actions · Answer 7

deeep

SSC Eights!

Points: 950

December 29, 2008 at 10:26 am

#918458

hi ALLL

deeep SSC Eights! Points: 950 More actions · Answer 8

hi ALLL i have a colomn keeping all the dates in the format YYYYDDMM .

my Requiremnt is to calculate the differnce of days from (YYYYMMDD) INT datatype column with the Current Date.

current date can be availabe iin the getdate() form ...

so issue is to calculate days differece (getdate()-YYYYMMDD)

so which will be the right way ..

Please help ,,,

i guess it will be better if calculate the difference in the YYYY-MM-DD HH:MM ,,because YYYYMMDD-YYYYMMDD is not giving Proper Days Difference

ps. One Orange Chip Points: 29422 More actions · Answer 9

Jeff Moden (12/29/2008)
Just so you know... storing dates in a database like that is a form of "Death by SQL"... it will bite you sometime in the future.
The only reason you should ever format dates in SQL Server is if the output is going to a file that requires it.

Deepak - Mark Jeff's word... U landed into trouble so soon...

Had you kept the dates in the native Sql server format, you just needed to use the DATEDIFF() function.....

If you're planning to segregate year, month and days from YYYYMMDD format, you ought to consider leap years as well 😛

I suggest you keep the date in a DATETIME feild rather than converting it into INT...

Pradeep Singh

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 10

It is definitely a huge mistake to store dates as integers. This is just one of the many issues you are going to run into - but, if you cannot change the column to a datetime (or date in SQL Server 2008) then I suggest you create a view or a computed column that casts the integer date to a valid datetime.

CREATE VIEW dbo.MyView AS

SELECT ...

,CAST(CAST(datecolumn AS char(8)) AS datetime) AS MyDateColumn

FROM dbo.MyTable

GO

You could also create a computed column on the table instead of using a view.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

PhilPacha Hall of Fame Points: 3645 More actions · Answer 11

Hmmm... Simple addition will NOT work. Multiplication is also needed.

Example 1: 2009 + 1 + 2 = 2012 (January 2, 2009)

Example 2: 2009 + 2 + 1 = 2012 (February 1, 2009)

Corrected: (2009 * 10000) + (1 * 100) + 2 = 20090102

Having noted that, dates should be stored AS dates unless there is a completely compelling reason. Even then, the best course is probably a computed column for the desired component(s). Whether the computed column should be actualized (physically stored in the database) depends on other factors, such as the need for indexing to facilitate processing.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

PhilPacha (12/29/2008)
Hmmm... Simple addition will NOT work. Multiplication is also needed.
Example 1: 2009 + 1 + 2 = 2012 (January 2, 2009)
Example 2: 2009 + 2 + 1 = 2012 (February 1, 2009)
Corrected: (2009 * 10000) + (1 * 100) + 2 = 20090102
Having noted that, dates should be stored AS dates unless there is a completely compelling reason. Even then, the best course is probably a computed column for the desired component(s). Whether the computed column should be actualized (physically stored in the database) depends on other factors, such as the need for indexing to facilitate processing.

All of that isn't necessary nor will it account for certain things like leap years, etc. The best way to do it is like Jeffrey Williams did... convert it to CHAR(8) and convert that to a real datetime which will account for leap years and all other manner of date based computational caveats. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 13

"Death by SQL'" ... hmmm ... does that make M$ both 'dangerous' and 'suicidal' for doing it in the msdb ???

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

jhadden SSCrazy Points: 2656 More actions · Answer 14

Oh grasshopper, you're digging a deep hole. All that is required as was stated earlier is DATEDIFF() using GETDATE() as one of your arguments, which will give you the difference between two datetimes in Years, Months, Days, Minutes, seconds, etc. as an integer. No need to go through all this conversion stuff.