VARCHAR as 09:20:00 Jan 30, 2013 PST. Covert Varchar to date in SQL

Question

How to convert varchar datetime value returned from paypal to Datetime in C#..

Below is the query I want to get the data which was not supported because of varchar datatype..

select * 
from table1 
where Month(date) = '05' 
  and Year(date) = '2021'

Here the date column's datatype is varchar as sample value (09:20:00 Jan 30, 2013 PST).

Can anyone help me to convert this varchar to datetime or date in SQL Server?

Answer 1

As I mentioned the problem is your design here. You are storing date and time values as a varchar; a grave mistake. Fix your design fix the problem. Assuming that all your values are in the format hh:mm:ss MMM dd, yyyy tz, and the time zone is significant, you could do something like this:

ALTER TABLE dbo.YourTable ADD NewDate datetimeoffset(0);
GO
CREATE TABLE #TimeZones (Timezone varchar(4),
                         Offset varchar(6));
--You need to create the rest of the timezones, these are examples
INSERT INTO #TimeZones
VALUES('PST','-08:00'),
      ('PDT','-07:00'),
      ('GMT','+00:00'),
      ('BST','+01:00'),
      ('CET','+01:00'),
      ('CEST','+02:00');
GO

UPDATE YT
SET NewDate = TRY_CONVERT(datetimeoffset(0),CONCAT(S.FormattedDateString,TZ.Offset))
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(REPLACE(SUBSTRING(YT.[date],10,13) + LEFT(YT.[date],9),',',''),(RIGHT(YT.[date],CHARINDEX(' ',REVERSE(YT.[date]))-1))))S(FormattedDateString,TimeZone)
     JOIN #TimeZones TZ ON S.TimeZone = TZ.TimeZone;
GO
DROP TABLE #Timezones;
GO
EXEC sys.sp_rename N'dbo.YourTable.Date',N'OldStringDate','COLUMN';
GO
EXEC sys.sp_rename N'dbo.YourTable.NewDate',N'Date','COLUMN';
GO

The original values will then be in OldStringDate so you can get a list of values that didn't convert with the following:

SELECT OldStringDate
FROM dbo.YourTable
WHERE OldStringDate IS NOT NULL
  AND [Date] IS NULL;

Then you can trivially get the data from your table with the following:

SELECT *
FROM dbo.YourTable
WHERE [Date] >= '2021-05-01T00:00:00-08:00'
  AND [Date] < '2021-06-01T00:00:00-08:00';