Convert GETDATE() to NUMBER

Question

Post reply

Convert GETDATE() to NUMBER

rahulsony111

Hall of Fame

Points: 3865
More actions
December 21, 2009 at 10:46 am

#207659

In a SSIS package i have a column with value GETDATE().
In target table column is defined as number(10,0).
How can i convert or cast?
I tried in internet but getting errors saying as invalid expression.
Please help me out

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on what your "number" is going to be;

SQL natively keeps the number of days since 01/01/1900:

select convert(decimal(12,0),getdate()) = 40167

select convert(decimal(12,4),getdate()) = 40166.5383

after the decimal point, it is the portion of one day(if 12:00 noon is 0,5, you can see i posted a bit later than that.

if your "number" is the number of seconds since a specific date, you'd have to tell us the starting date. how is your number going to represent time, if it is not a datetime column?

why not store the date no matter what? it's much more accurate and saves the whole convert from datetime to-number-back-todatetime problem.

does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 2

Do you know which datatype returns only date like 2009-12-21

I want to convert that date to 20091221

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 3

SQL Server is going to return GetDate as a datetime - which includes the date and the time.

Since you are wanting this in a very specific format, you might want to use the datepart functions to pull out the pieces of the date and put them back together. This will do that in the format you want - including zero padding the month and day if they are one digit.

select cast(

cast(datepart(year,getdate()) as varchar) + -- get the year

right('0' + cast (datepart(month,getdate()) as varchar),2) + --get the month and zero pad

right('0' + cast (datepart(day,getdate()) as varchar),2) --get the day and zero pad

as numeric(10,0)) -- convert back to a numeric(10,0)

Lowell SSC Guru Points: 323518 More actions · Answer 4

that is exactly why you'd want to not store a date as a number: 20091221 you can kind of read as a date, but what if the value is 20091250 20099999??

you are using a number and assuming that certain pairs of digits should not exceed #allowed days/#allowed months. it is bad practice. always store dates as dates.

at some point you'll be back saying how do i convert 20091221 into a datetime.

anyway, yo can get the value you want as part of a convert statement:

SELECT CONVERT(VARCHAR(35),@date,112)

--results = '20091221'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 5

Have you tried converting to varchar with style 112?

select convert(varchar(100), getdate(), 112);

You could wrap that in a further convert/cast to numeric, if you want to.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Or look at the other thread on that rahulsony111 started

http://www.sqlservercentral.com/Forums/Topic837454-148-1.aspx#bm837479

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

I believe it's already been said on one of these threads but there are a thousand reasons why storing an ISO date as a numeric is absolutely the wrong thing to do. I'd try to compel the designers of such a travesty to change their mind.

--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)