May 23, 2012 at 5:41 am
How can I merge 2 SQL statements below to render --> TODAY's date - 13 months --> in the format: MM/DD/YYYY
for example: If today is June 1, 2012 -- I need the following: 05/01/2012
here is TODAY -13 month calculation:
----- SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(MONTH,-13,GETDATE())), 0)
here is the MM/DD/YYYY format I need:
----- SELECT CONVERT(VARCHAR, GETDATE(),101)
thanks in advance !
May 23, 2012 at 5:42 am
1 correction (I mis-typed the date needed value above)
If today is June 1, 2012, I need 05/01/2011
May 23, 2012 at 5:45 am
Just need to add the convert to the end of the dataadd/difs
SELECT CONVERT(VARCHAR,DATEADD(dd,DATEDIFF(dd,0,DATEADD(MONTH,-13,GETDATE())),0),101)
May 23, 2012 at 5:47 am
PERFECT! Exactly what I was looking for.. thanks a million.
May 24, 2012 at 4:51 am
I've coded a process to DELETE rows > 13 months old. The date in the SQL table is first populated (replicated) from an older Oracle db w/ the old Oracle date datatype. (Oracle is deprecating this datatype as well)
The actual SQL column datatype housing the Oracle replicated date is defined as INT. (a relative number representing Oracle date)
To determine qualifying date (> 13 months old) I apply the following:
--******************************************************************************
-- Purge 05 SQLDatabase.dbo.Call_Detail_Record
--******************************************************************************
-- DELETE all rows > 13 months old; Apply Oracle relative date conversion using UTCOffSet
--
BEGIN TRAN Purge_05
DECLARE @UTCOffSet SMALLINT
SELECT @UTCOffSet = DateDiff(hh,GetUTCDate(),GetDate())
WHILE (1=1)
BEGIN
DELETE TOP (1000) FROM SQLDatabase.dbo.Call_Detail_Record from SQLDatabase.dbo.Call_Detail_Record C
WHERE dateadd(ss,dateTimeDisconnect+(@UTCOffSet*3600),'1/1/1970') < (SELECT CONVERT(VARCHAR,DATEADD(dd,DATEDIFF(dd,0,DATEADD(MONTH,-13,GETDATE())),0),101))
IF @@rowcount = 0 -- No row affected.
BREAK
ELSE
Continue
END
COMMIT TRAN Purge_05
If you know a better way to render the Oracle INT date value > 13 months old, please share.
May 24, 2012 at 5:19 am
CELKO (5/23/2012)
...Why do you want to do formatting in the database?
...
Because MS introduced FORMAT function into SQL2012 . May be they done it to pick on you...
:hehe:
... Besides missing a fundamental concept, you did not use the ISO-8601 temporal data format. It is the only one allowed in ANSI/ISO Standard SQL. It is covered in the first hour of your SQL class on temporal data.
...
Just would like to remind, that MS SQL Server T-SQL is not ANSI/ISO Standard SQL!
+ I never took SQL class on temporal data (3-years of village Sunday-church school that is all education I have :()
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply