Convert GETDATE() to YYYYMMDD

Question

Post reply

Convert GETDATE() to YYYYMMDD

rahulsony111

Hall of Fame

Points: 3865
More actions
December 21, 2009 at 12:23 pm

#213830

In derived columns i have to convert getdate() to YYYYDDMM.
I dont need time.
I want it in format like 20091221
Please help me

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Curious, how many different ways do you plan on asking the same question?

You have asked the same question here and here just worded it a little different.

May I suggest that you pick one thread and concentrate on that one so that you don't get the same answers and have to answer the same questions multiple times?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

By the way, I'd store a date as a date, not a numeric value.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I'm curious what the need is to convert a date to a Number. I would leave the date as one of the datetime datatype in sql 2005.

Now to get the Date Format you seek, you will need to convert to varchar and check the following: http://www.sqlusa.com/bestpractices2005/centurydateformat/

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

CirquedeSQLeil (12/21/2009)
I'm curious what the need is to convert a date to a Number. I would leave the date as one of the datetime datatype in sql 2005.
Now to get the Date Format you seek, you will need to convert to varchar and check the following: http://www.sqlusa.com/bestpractices2005/centurydateformat/

Actually, on the SQL side dropping the time portion off getdate is this simple:

dateadd(dd, datediff(dd, 0, getdate()), 0)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Lynn Pettis (12/21/2009)
CirquedeSQLeil (12/21/2009)
I'm curious what the need is to convert a date to a Number. I would leave the date as one of the datetime datatype in sql 2005.
Now to get the Date Format you seek, you will need to convert to varchar and check the following: http://www.sqlusa.com/bestpractices2005/centurydateformat/
Actually, on the SQL side dropping the time portion off getdate is this simple:
dateadd(dd, datediff(dd, 0, getdate()), 0)

Noted and added to my toolbox - I had never used that method. Thanks Lynn.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

CirquedeSQLeil (12/21/2009)
Lynn Pettis (12/21/2009)
CirquedeSQLeil (12/21/2009)
I'm curious what the need is to convert a date to a Number. I would leave the date as one of the datetime datatype in sql 2005.
Now to get the Date Format you seek, you will need to convert to varchar and check the following: http://www.sqlusa.com/bestpractices2005/centurydateformat/
Actually, on the SQL side dropping the time portion off getdate is this simple:
dateadd(dd, datediff(dd, 0, getdate()), 0)
Noted and added to my toolbox - I had never used that method. Thanks Lynn.

Check my blog for some other date routines. They might come in handy as well.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks - will do.

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

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

I am trying this from 5 hrs and unable to get a solution.

In target table i have that column as numeric.

I tried getdate() with datatype databasedate but it is returning 2009-12-21. So that value is not going into target table sand saying it as invalid number.

I want it ti look like 20091221, only then it goes into targat table

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

rahulsony111 (12/21/2009)
I am trying this from 5 hrs and unable to get a solution.
In target table i have that column as numeric.
I tried getdate() with datatype databasedate but it is returning 2009-12-21. So that value is not going into target table sand saying it as invalid number.
I want it ti look like 20091221, only then it goes into targat table

As mentioned in one of the other threads where you asked the "same" question, and since you seem fixed on storing the date as an integer, in SQL:

cast(convert(varchar(8), getdate(), 112) as int)

GSquared SSC Guru Points: 260824 More actions · Answer 10

rahulsony111 (12/21/2009)
I am trying this from 5 hrs and unable to get a solution.
In target table i have that column as numeric.
I tried getdate() with datatype databasedate but it is returning 2009-12-21. So that value is not going into target table sand saying it as invalid number.
I want it ti look like 20091221, only then it goes into targat table

First, I already posted a solution on one of your other copies of this question. (By the way, please don't do that. Just post each question once. Duplicate posting is annoying.)

Second, this forum doesn't have any sort of time limit or service level agreement. You get answers as we have the time, willingness, and knowledge, to post them.

Third, storing dates as formatted numbers is a really bad idea. I gave you an answer on how to do it, but please do understand that you're going to cause more problems than you solve by doing so.

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

Gus has some valid points. Amongst those is the double posting. When posting the question, if you double post - it will just add to your frustration as the answer may be posted in one of the threads that you are not currently checking.

It appears that several valid responses have been made - please try those and let us know the results.

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

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

cast(convert(varchar(8), getdate(), 112) as int)

it didn't work for me man

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

rahulsony111 (12/21/2009)
cast(convert(varchar(8), getdate(), 112) as int)
it didn't work for me man

Worked for me when I ran it in SSMS. What error did you get?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Lynn Pettis (12/21/2009)
rahulsony111 (12/21/2009)
cast(convert(varchar(8), getdate(), 112) as int)
it didn't work for me man
Worked for me when I ran it in SSMS. What error did you get?

It won't work in SSIS... not sure what will but it's another reason why I hate SSIS. T-SQL usually doesn't work there.

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