February 28, 2008 at 4:51 am
tweety (10/22/2007)
I have a Date column as MM/DD/YYYY(datetime) i need to convert this to YYYYMMDD(int). Can anyone help me in converting this....
It may help to sort out some common misunderstandings and misconceptions...
The above question, which is all too common, is fundamentally wrong in one aspect.
Many folks thinks they have something that they don't. So in reality, there probably is no problem where one may think there is one.
The 'wrong part' is this: Date column as MM/DD/YYYY(datetime)
The datetime datatype does not store dates in any of the formats that we humans read as dates.
This is very important to understand.
This gives then that if we have a datetime, then it's not stored as mm/dd/yyyy or yyyy-mm-dd or whatever you may see.
All formats that we read as dates are for display only when used for output.
It's not the same thing as how the date is actually stored.
Knowing this would instantly solve many 'store a date in a certain format' problems 😉
(there is no problem with it)
Also adding to others, that storing a date as an int is just going to cause grief along the line.
Treat a date as a date and handle it accordingly.
That's what we have types for.
/Kenneth
February 29, 2008 at 9:19 am
VAIYDEYANATHAN.V.S (10/25/2007)
try this!!!!!!!!!!
http://www.pcreview.co.uk/forums/thread-1780991.php%5B/quote%5D
The link relates specifically to Excel and is of little or no use in this context.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 29, 2008 at 10:55 am
One perfectly acceptable case where this format would work is in a Date dimension for a data warehouse environment.
While it is advisable in most cases to use a meaningless INT key for dimension tables, the date dimension key can be stored as YYYYMMDD. This provides an easy way for the business user to see what date the specific record represents.
My two cents...
Dan
May 1, 2008 at 8:38 am
select substring(cast(settledate as varchar(10)),6,2)+ '/'+ substring(cast(settledate as varchar(10)),4,2)+ '/' + substring(cast(settledate as varchar(10)),1,4)as SettleDate from table1
Hope this shold work.
Thanks,
Hanu
July 17, 2008 at 4:59 pm
I have to agree with Dan that YYYYMMDD as an integer is useful as a key for a date dimension in a data warehouse.
It also happens to be a common way that dates are stored in tables on an AS/400 (aka iSeries ).
August 21, 2008 at 8:33 pm
DECLARE @dtDate datetime
SET @dtDate = getdate() - FLOOR( CAST( getdate() AS float ))
SELECT
@dtDate AS [YYYY-MM-DD HH:MM:SS.SSS]
, CAST( FLOOR( CAST( @dtDate AS float )) AS datetime ) AS [YYYY-MM-DD 00:00:00.000]
, CAST( CONVERT( varchar(10), @dtDate, 112 ) AS int ) AS [YYYYMMDD]
, CAST( CONVERT( datetime , @dtDate , 120 ) as varchar(11)) AS [Mon _D YYYY]
, STUFF( CAST( CONVERT( datetime , @dtDate , 120 ) as varchar(11)) , 7 , 1 , ', ' ) AS [Mon _D, YYYY]
, LEFT( DATENAME( MONTH , @dtDate ) , 3 ) + ' '
+ RIGHT( '0' + CAST( DATEPART( dd , @dtDate ) AS varchar ) , 2 ) + ', '
+ CAST( DATEPART( yyyy , @dtDate ) AS varchar ) AS [Mon 0D, YYYY]
February 23, 2012 at 5:02 am
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
--yyymmdd -20120128
,CAST(h.run_date as int ) date_convert, convert(char(10),DATEADD(d, -7, GETDATE()),112) TARGET,h.run_status
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
---- convert(datetime,'20091231', 112)
---- convert(char(10),Date,112)
AND h.run_date > convert(char(10),DATEADD(d, -7, GETDATE()),112)
ORDER BY h.run_date DESC
February 23, 2012 at 5:03 am
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
--yyymmdd -20120128
,CAST(h.run_date as int ) date_convert, convert(char(10),DATEADD(d, -7, GETDATE()),112) TARGET,h.run_status
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
---- convert(datetime,'20091231', 112)
---- convert(char(10),Date,112)
AND h.run_date > convert(char(10),DATEADD(d, -7, GETDATE()),112)
ORDER BY h.run_date DESC
February 23, 2012 at 5:13 am
May be I'm missing something, but requested YYYYMMDD is the ISO format supported by CONVERT:
SELECT CONVERT(VARCHAR,GETDATE(),112)
The above will convert any DATETIME value into VARCHAR formatted as requested!
Then you can cast it as INT:
SELECT CAST(CONVERT(VARCHAR,GETDATE(),112) AS INT)
March 4, 2012 at 9:03 pm
BWWWAAAA_HAAAA!!!! I know this thread is old but I think it's a funny thread. Let me replay the whole thing for you...
We have a team that wants to commit SQL and Application genecide by storing dates as an ISO formatted integer followed by many people who obliged with a couple of folks that shouted danger as they helped plunge the knife followed by one person that tried to justify the murder followed by many more people who didn't read the thread to find the problem had already been murdered many times by the earlier many people all of which committed the same code murder of formatting a date as an integer and would probably bitch out loud if they ever had to deal with the dates and times stored in jobs history table in MSDB.
Ya just can't make up a good murder story like this... it had to happen. 😀 Now, THAT's entertainment. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2012 at 3:14 am
Jeff Moden (3/4/2012)
BWWWAAAA_HAAAA!!!! I know this thread is old but I think it's a funny thread. Let me replay the whole thing for you...We have a team that wants to commit SQL and Application genecide by storing dates as an ISO formatted integer...
From what I can read in the first OP post, I cannot make an assumption that the this team wants to store date as ISO formatted integer. I guess his question was about how to use CONVERT.
I would assume that OP has requirements to produce some sort of report or feed which must have date in ISO format. OP tried hard to use CONVERT function as VB-like format - and it didn't work for him for some reason :-).
The implementatition may require to store data in the work table before extracting or reporting, so he might need to insert it somewhere for temporary storage...
March 5, 2012 at 6:48 am
Sorry for going so far out of character on this one, Eugene. I was absolutely amazed by what happened here.
Look further down in the thread. Here's the post in this thread that really started it for me.
tweety (10/23/2007)
ya, but my team want that to be an int value...
There were some good people that strongly recommended against it but it was too late. The OP had already "solved" the problem using CONVERT. The thing that struck me most funny was all the people that dog-piled on this thread after that with other/the same "solutions" even though at least 4 people warned of the impending problems.
If you look at the post that Luke L captured...
bharani (10/23/2007)
ya, but my team want that to be an int value...
... the op even changed his handle after this one. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2012 at 4:38 am
This is standard practice in date dimensions in a data warehouse, having a timekey of YYYYMMDD (int)
June 21, 2012 at 8:41 am
adolf garlic-411709 (6/19/2012)
This is standard practice in date dimensions in a data warehouse, having a timekey of YYYYMMDD (int)
It is not!
This is not a STANDARD practice - it is an individual preference (and not the best one).
There are plenty of data warehouses around (pre sql2008) which use non-formatted INT as a key to DATE dimension.
Converting YYYYMMDD formatted INT value back to datetime will require double-conversion: 1-st to varchar and only then to datetime.
Using not-formatted INT value has no such problem as it can be implicitly converted to date by SQL:
SELECT DATEADD(day,1,41080)
Plus "date add" arithmetic will work as per normal math:
SELECT CAST(41080 as DATETIME), CAST(41080 + 10 as DATETIME)
Also, if you convert date (without time portion) to INT you will have exactly the same value:
SELECT CAST(GETDATE() AS INT)
The main reason INT used there was to save 4 byte per datetime value in pre SQL2008 databases. Since SQL2008 introduced 4-byte DATE datatype, there is no needs in using INT for this purpose any more.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply