CODEWITHSUNDEEP
sql-server
Qfriend
Qfriend

Reputation: 31

Date convertion Issue in Ms sql

My table name as tbl_event

enter image description here

Event_ExpiryDate, Event_Date fields data type is varchar, I have using the following query for getting data from the table:

select * from tbl_event
WHERE 
LEFT (CONVERT(VARCHAR,Event_ExpiryDate,103),12)>= LEFT(CONVERT(varchar,GetDate(),103),12) and
 LEFT (CONVERT(VARCHAR,Event_Date,103),12)<= LEFT(CONVERT(varchar,GetDate(),103),12)

but I am getting a result like this

enter image description here

1 data to be missing. Why? this issue only on some month's date only. Months are 2,4,8,12

Upvotes: 0

Views: 55

Answers (2)

Thom A
Thom A

Reputation: 95949

The real problem here is your data, and that you're using the wrong data type. Fix the data type, fix the problem.

You can fix that by firstly changing your varchar representation of a date to an unambiguous Date format, yyyyMMdd (This assumes your dates are in the format dd/MM/yyyy):

UPDATE dbo.tbl_event
SET Event_Date = CONVERT(varchar(8),CONVERT(date,Event_Date,103),112),
    Created_Date = CONVERT(varchar(8),CONVERT(date,Created_Date,103),112),
    Event_ExpiryDate = CONVERT(varchar(8),CONVERT(date,Event_ExpiryDate,103),112);

Then you can ALTER the table to fix your data types:

ALTER TABLE dbo.tbl_event ALTER COLUMN Event_Date date NULL; --Use NOT NULL if not NULLable
ALTER TABLE dbo.tbl_event ALTER COLUMN Created_Date date NULL; --Use NOT NULL if not NULLable
ALTER TABLE dbo.tbl_event ALTER COLUMN Event_ExpiryDate date NULL; --Use NOT NULL if not NULLable

Note: If any of your dates have bad values, for example '31/04/2019', the above will fail. You can get around this by changing the CONVERT functions to TRY_CONVERT, however any values that fail to convert will have the value NULL. If your columns have the NOT NULL property you will need to ensure you handle that too. If you do have bad values in your table, I strongly suggest taking a backup, or copy of the database/table first, so that you have a historical copy. (Of course, does a date like '31/04/2019' or '12/13/2018' have any meaning anyway?)

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use only convert() :

where convert(date, Event_ExpiryDate, 103) >= convert(date, getdate()) and
      convert(date, Event_Date, 103) <= convert(date, getdate());

However, storing varchar date is really bad idea. It will lead you lots of in trouble.

Upvotes: 0

Related Questions