December 23, 2008 at 11:18 pm
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,
December 23, 2008 at 11:41 pm
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
December 23, 2008 at 11:45 pm
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
December 28, 2008 at 7:59 pm
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
December 29, 2008 at 5:39 am
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
Change is inevitable... Change for the better is not.
December 29, 2008 at 5:44 am
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 😛
December 29, 2008 at 10:26 am
hi ALLL
December 29, 2008 at 10:37 am
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
December 29, 2008 at 10:54 am
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...
December 29, 2008 at 11:22 am
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
December 29, 2008 at 11:55 am
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.
December 29, 2008 at 6:06 pm
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
Change is inevitable... Change for the better is not.
December 30, 2008 at 10:16 am
"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."
December 30, 2008 at 1:57 pm
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply