Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Monday, June 30, 2014 8:32 AM
Points: 194, Visits: 547
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 @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #837414
Posted Monday, December 21, 2009 12:40 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 21,733, Visits: 15,424
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #837483
Posted Monday, December 21, 2009 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:02 PM
Points: 37,062, Visits: 31,625
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Add to briefcase

Permissions Expand / Collapse