Reputation: 212
I am currently working on a query that needs to calculate the difference in days between two different dates. I've had issues with our DATE
columns before, because they are all being stored as numeric
columns which is a complete pain.
I tried using CONVERT
as I had done in the past to try and get the different pieces of the DATETIME
string built, but I am not having any luck.
The commented line --convert(datetime,)
is where I am having the issue. Basically, I need to convert PO_DATE
and LINE_DOCK_DATE
to a format that is usable, so I can calculate the difference between the two in days.
USE BWDW
GO
SELECT
[ITEM_NO]
,[ITEM_DESC]
,[HEADER_DUE_DATE]
,[BWDW].[dbo].[DS_tblDimWhs].WHS_SHORT_NAME AS 'Warehouse'
,[BWDW].[dbo].[DS_tblFactPODtl].[PO_NO] AS 'PO NUMBER'
,[BWDW].[dbo].[DS_tblFactPODtl].[PO_DATE] AS 'Start'
,[BWDW].[dbo].[DS_tblFactPODtl].[PO_STATUS] AS 'Status'
,[BWDW].[dbo].[DS_tblFactPODtl].[LINE_DOCK_DATE] AS 'End'
--,(SELECT CONVERT(DATETIME, CONVERT(CHAR(8), [BWDW].[dbo].[DS_tblFactPODtl].[PO_DATE])) FROM dbo.DS_tblFactPODtl)
FROM [BWDW].[dbo].[DS_tblFactPODtl]
INNER JOIN [BWDW].[dbo].[DS_tblDimWhs] ON [BWDW].[dbo].[DS_tblFactPODtl].WAREHOUSE = [BWDW].[dbo].[DS_tblDimWhs].WAREHOUSE
INNER JOIN [BWDW].[dbo].[DS_tblFactPO] ON [BWDW].[dbo].[DS_tblFactPODtl].PO_NO = [BWDW]. [dbo].[DS_tblFactPO].PO_NO
WHERE [BWDW].[dbo].[DS_tblFactPODtl].[PO_STATUS] = 'Closed'
AND [BWDW].[dbo].[DS_tblFactPODtl].[LINE_DOCK_DATE] <> 0
I have a snippet I saved from a previous project I worked on that needed to only display results from today through another year. That had a bunch of CAST and CONVERTS in it, but I tried the same methodology with no success.
In the long run, I want to add a column to each database table to contain a proper datetime
column that is usable in the future... but that is another story. I have read numerous posts on stackoverflow that talk about converting to NUMERIC and such, but nothing out of a NUMERIC back to DATETIME
.
Example data:
Start | End | Difference
--------------------------------
20110501 | 20111019 | 171
20120109 | 20120116 | 7
20120404 | 20120911 | 160
Just trying to calculate the difference..
MODIFIED PER AARON:
SELECT
FPODtl.[ITEM_NO] AS [Item]
,FPODtl.[ITEM_DESC] AS [Description]
,D.WHS_SHORT_NAME AS [Warehouse]
,FPODtl.[PO_NO] AS [PO NUMBER]
,FPODtl.[PO_DATE] AS [Start]
,FPODtl.[PO_STATUS] AS [Status]
,FPODtl.[LINE_DOCK_DATE] AS [End]
,DATEDIFF
(
DAY,
CASE WHEN ISDATE(CONVERT(CHAR(8), FPODtl.PO_DATE)) = 1
THEN CONVERT(DATETIME, CONVERT(CHAR(8), FPODtl.PO_DATE)) END,
CASE WHEN ISDATE(CONVERT(CHAR(8), FPODtl.[LINE_DOCK_DATE])) = 1
THEN CONVERT(DATETIME, CONVERT(CHAR(8), FPODtl.[LINE_DOCK_DATE])) END
)
FROM [dbo].[DS_tblFactPODtl] AS FPODtl
INNER JOIN [dbo].[DS_tblDimWhs] AS D
ON FPODtl.WAREHOUSE = D.WAREHOUSE
INNER JOIN [dbo].[DS_tblFactPO] AS FPO
ON FPODtl.PO_NO = FPO.PO_NO
WHERE FPODtl.[PO_STATUS] = 'Closed'
AND FPODtl.[LINE_DOCK_DATE] <> 0;
Upvotes: 3
Views: 74957
Reputation: 280625
DECLARE @x NUMERIC(10,0);
SET @x = 20110501;
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), @x));
Result:
2011-05-01 00:00:00.000
To compare two:
DECLARE @x NUMERIC(10,0), @y NUMERIC(10,0);
SELECT @x = 20110501, @y = 20111019;
SELECT DATEDIFF
(
DAY,
CONVERT(DATETIME, CONVERT(CHAR(8), @x)),
CONVERT(DATETIME, CONVERT(CHAR(8), @y))
);
Result:
171
More importantly, fix the table. Stop storing dates as numbers. Store them as dates. If you get errors with this conversion, it's because your poor data choice has allowed bad data into the table. You can get around that - potentially - by writing the old version of TRY_CONVERT()
:
SELECT DATEDIFF
(
DAY,
CASE WHEN ISDATE(col1)=1 THEN CONVERT(DATETIME, col1) END,
CASE WHEN ISDATE(col2)=1 THEN CONVERT(DATETIME, col2) END
)
FROM
(
SELECT
col1 = CONVERT(CHAR(8), col1),
col2 = CONVERT(CHAR(8), col2)
FROM dbo.table
) AS x;
This will produce nulls for any row where there is garbage in either column. Here is a modification to your original query:
SELECT
[ITEM_NO] -- what table does this come from?
,[ITEM_DESC] -- what table does this come from?
,[HEADER_DUE_DATE] -- what table does this come from?
,D.WHS_SHORT_NAME AS [Warehouse] -- don't use single quotes for aliases!
,FPODtl.[PO_NO] AS [PO NUMBER]
,FPODtl.[PO_DATE] AS [Start]
,FPODtl.[PO_STATUS] AS [Status]
,FPODtl.[LINE_DOCK_DATE] AS [End]
,DATEDIFF
(
DAY,
CASE WHEN ISDATE(CONVERT(CHAR(8), FPODtl.PO_DATE)) = 1
THEN CONVERT(DATETIME, CONVERT(CHAR(8), FPODtl.PO_DATE)) END,
CASE WHEN ISDATE(CONVERT(CHAR(8), FPODtl.[LINE_DOCK_DATE])) = 1
THEN CONVERT(DATETIME, CONVERT(CHAR(8), FPODtl.[LINE_DOCK_DATE])) END
)
FROM [dbo].[DS_tblFactPODtl] AS FPODtl
INNER JOIN [dbo].[DS_tblDimWhs] AS D
ON FPODtl.WAREHOUSE = D.WAREHOUSE
INNER JOIN [dbo].[DS_tblFactPO] AS FPO
ON FPODtl.PO_NO = FPO.PO_NO
WHERE FPODtl.[PO_STATUS] = 'Closed'
AND FPODtl.[LINE_DOCK_DATE] <> 0;
Upvotes: 9
Reputation: 70678
If the date stored as a number is like this: 20130226
for today, then the simpler way to convert to DATE
or DATETIME
would be:
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),NumberDate),112)
Upvotes: 2
Reputation: 8113
Here is a quick formula to create a date from parts :
DateAdd( Month, (( @Year - 1900 ) * 12 ) + @Month - 1, @Day - 1 )
Simply use substrings from your original field to extract @Year
, @Month
and @Day
. For instance, if you have a numeric like 19531231
for december 31th, 1953, you could do :
DateAdd( Month, (( SubString(Cast(DateField As Varchar(8)), 1, 4) - 1900 ) * 12 ) +
SubString(Cast(DateField As Varchar(8)), 5, 2) - 1,
SubString(Cast(DateField As Varchar(8)), 7, 2) - 1 )
Upvotes: 0