Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert GETDATE() to NUMBER Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 10:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
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
Post #837385
Posted Monday, December 21, 2009 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 14,445, Visits: 37,857
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!
Post #837389
Posted Monday, December 21, 2009 11:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
Do you know which datatype returns only date like 2009-12-21
I want to convert that date to 20091221
Post #837401
Posted Monday, December 21, 2009 11:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 2, 2015 11:05 AM
Points: 198, Visits: 574
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)

Post #837411
Posted Monday, December 21, 2009 11:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 14,445, Visits: 37,857
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!
Post #837414
Posted Monday, December 21, 2009 12:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 13,999, Visits: 9,725
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
Post #837478
Posted Monday, December 21, 2009 12:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 19,921, Visits: 18,140
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
Post #837483
Posted Monday, December 21, 2009 8:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 40,994, Visits: 38,293
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #837715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse