Query to convert VARCHAR to DATETIME in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

When i run this query im getting last month details and this month details also. i want only this month data only as Date column is in varchar and data is saving in DD-MM-YYYY Formate. Check out below table.

Bill_No     Name                               TotalAmount     Date

13  Lakshmi Devi Jatra Mahotsava     500.00          08-07-2018
14  Chandru Kmatagi                       1800.00         08-07-2018
15  Nurani Shedaji                             780.00         08-07-2018
15  Nurani Shedaji                             780.00          08-07-2018
16  Design Charge                              500.00           08-07-2018
17  Arun                                             90.00              08-07-2018
70  Honnutagi Banner                        900.00             08-08-2018
71  B.L.D.E College                           720.00              08-08-2018

Posted one year ago Modified on one year ago

Hi Kirron,

Refer below test query.

SQL

DECLARE @Test AS TABLE(Bill_No INT,Name VARCHAR(50),TotalAmount DECIMAL(10,2),Date VARCHAR(10))
INSERT INTO @Test VALUES(13,'Lakshmi Devi Jatra Mahotsava','500.00','08-07-2018')
INSERT INTO @Test VALUES(14,'Chandru Kmatagi','1800.00','08-07-2018')
INSERT INTO @Test VALUES(15,'Nurani Shedaji','780.00','08-07-2018')
INSERT INTO @Test VALUES(15,'Nurani Shedaji','780.00','08-07-2018')
INSERT INTO @Test VALUES(16,'Design Charge','500.00','08-07-2018')
INSERT INTO @Test VALUES(17,'Arun','90.00','08-07-2018')
INSERT INTO @Test VALUES(70,'Honnutagi Banner','900.00','08-08-2018')
INSERT INTO @Test VALUES(71,'B.L.D.E College','720.00','08-08-2018')

SELECT * FROM @Test 
WHERE DATEPART(MONTH,CONVERT(DATETIME,Date,103)) = DATEPART(MONTH,GETDATE())

Output

Bill_No Name TotalAmount Date
70 Honnutagi Banner 900.00 08-08-2018
71 B.L.D.E College 720.00 08-08-2018