SQL DateTime Conversion & Other Related

Most of the time you may need to select records from table based on date filteration. Date given in UI is mostly in any of the 9 total possible combinations and mostly your UI date is not in the format of the MSSQL datetime format stored in the database i.e in this format

yyyy-mm-dd hh:mm:ss.mmm

You need to convert the date from UI to these format for filtering the date fields.

1. Convert your UI date to format yyyy/mm/dd

2. Then in the sql filtering condition give like this CONVERT(DATETIME,'uidateYear/uiDateMonth/uiDay',121)

Example:

DECLARE @tblVar TABLE (
ID INT IDENTITY(1,1),
LogDate DATETIME,
ErrorDescription VARCHAR(MAX)
)

INSERT INTO @tblVar VALUES (DATEADD(d,0,getdate()),'
Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,1,getdate()),'Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,2,getdate()),'Description1')
INSERT INTO @tblVar VALUES (DATEADD(d,3,getdate()),'Description1')



select * from @tblVar where CONVERT(VARCHAR(10),LogDate,111) between
CONVERT(VARCHAR(10),'2011/05/25',111) and
CONVERT(VARCHAR(10),'2011/05/27',111)

To Filter records based on yyyy/mm/dd

------------------------------------------

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

SELECT * FROM Table WHERE CONVERT(VARCHAR(10),DateField,111) = CONVERT(VARCHAR(10),GETDATE(),111)

For More Reference look here