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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

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

?

1 2 3

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