CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
omar K
omar K

Reputation: 245

Altering the date when displaying data

I am working with a severely large SQL Server database and it's Date column is designed to be displayed as such:

Date(numeric(17,9), not null):

20130623.143448060

REQUIRED OUTPUT

That same Date translates to:

6/23/2013 10:34:48 AM

My Current Query is:

  SELECT ItemId, Date, Object, SourceSite, DestSite FROM JnlMediumMove WHERE UserCode = 'Automation' AND LEFT(Date,8)=CONVERT(VARCHAR(8),GETDATE()-1,112) Order By Date Desc;

All the dates under the Date column that is displayed in this query is what needs to be altered to the required Output. I just have two requirements, 1) UserCode is Automation and 2) Only the entries that were inputted 1 day before the current day are displayed. (Only interested in those entries otherwise too many outputs are displayed)

I was wondering if there is a SQL query which will turn that Date into the format of the required output?

Upvotes: 0

Views: 106

Answers (1)

Lamak
Lamak

Reputation: 70668

As many comments said, it's a bad idea to store dates this way. Nevertheless, here is one way to cast your numeric value to a DATETIME one:

DECLARE @Date NUMERIC(17,9)

SET @Date = 20130623.143448060

SELECT  DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT(YourDate,8)+' '+
                        SUBSTRING(YourDate,10,2)+':'+
                        SUBSTRING(YourDate,12,2)+':'+
                        SUBSTRING(YourDate,14,2)+'.'+
                        SUBSTRING(YourDate,15,3))) YourDate
FROM (  SELECT CONVERT(VARCHAR(18),@Date) YourDate) A

Result:

╔═════════════════════════╗
║        YourDate         ║
╠═════════════════════════╣
║ 2013-06-23 10:34:48.807 ║
╚═════════════════════════╝

From here, you can format that date as you please.

UPDATED

If you need to use this transformation on the Date column, then just use:

SELECT  ItemId,
        DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
                        SUBSTRING([Date],10,2)+':'+
                        SUBSTRING([Date],12,2)+':'+
                        SUBSTRING([Date],14,2)+'.'+
                        SUBSTRING([Date],15,3))) [Date],
        [Object], 
        SourceSite, 
        DestSite
FROM (  SELECT  ItemId,
                CONVERT(VARCHAR(18),[Date]) [Date],
                [Object],
                SourceSite,
                DestSite,
                            UserCode
        FROM JnlMediumMove) A
WHERE UserCode = 'Automation' 
AND LEFT([Date],8)=CONVERT(VARCHAR(8),GETDATE()-1,112) 
ORDER BY [Date] DESC;

Upvotes: 3

Related Questions