Convert Date Stored as NUMERIC to DATETIME

问题:

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;

回答1:

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;

回答2:

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)

回答3:

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 )