How to compare 2 formatted fields and using them for where clause condition-CodePudding

I have been trying to compare 1 field to itself by converting it to 'ddMMyyyy' and trying to execute it

Data example:

User    | DateTime
AU      | 01-04-2022 11:11:11
AU      | 01-04-2022 12:09:30
SG      | 04-08-2023 03:23:01
NA      | 21-11-2022 15:10:40

I've tried doing this, DateTime consist of a few data of date and time where the original format is "01-01-2022 11:12:09"

select *
    format(DateTime, 'ddMMyyyy') AS DT
where DT != DT FROM TblUser

are there any way to do this?

My expectation on this would be that it will show the fields where DT does not have the same value of itself.

Expected result would be:

User    | DateTime
SG      | 04082023
NA      | 21112022

CodePudding user response：

One option is using WITH TIES.

I don't like using format(), especially with large tables. Personally, I would just convert(date,[datetime])

Example

Select top 1 with ties 
       [User]
      ,[DateTime] = format([Datetime],'ddMMyyyy')
 From TblUser
 Order By sum(1) over (partition by [User],convert(date,[DateTime]))

Results

User    DateTime
NA      21112022
SG      04082023

EDIT: If by chance your [DateTime] column is a string

Select top 1 with ties 
       [User]
      ,[DateTime] = replace(left([DateTime],10),'-','')
 From TblUser
 Order By sum(1) over (partition by [User],left([DateTime],10))

CodePudding user response：

For the comparison I am assuming that you want rows where the "date" is without the time of day. (In reality, if the data type is datetime then any "just date" will also have the time of 00:00:00.000.) So, to ignore the time of day (except those with time NOT at 00:00:00.000) then convert the date type from datetime to date.

nb: The clauses must be in order SELECT then FROM then WHERE

SELECT
      TblUser.*
    , format([DateTime], 'ddMMyyyy') AS DT
FROM TblUser
WHERE [DateTime] != convert(date,[DateTime])

If you really needed to use the column alias "DT" then you need a derived table like this:

SELECT
    format(DT, 'ddMMyyyy') AS DTfinal
FROM (
    SELECT
          TblUser.*
        , convert(date,[DateTime]) AS DT -- the "column alias" is created here
    FROM TblUser
     ) AS derived
WHERE DT != [DateTime] -- now you can use the "column alias" DT

BUT do not use format() when forming the column "DT" because you cannot reliably compare the string (in ddMMyyyy format) to a date or a datetime value.

nb: the format() function returns a string even if you comprehend ddMMyyyy to represent a date.