I tried to extract the data based on date (here the date is in varchar datatype) but an error raised while extracting
the error is : Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Now I need help to find the record in which is error prompt from the data.
Date field having record like '01/04/2013 03:24:50'
Please help me
'01/04/2013' is ambiguous - it might be 01-April or 04-January. SQL will make a "guess" which format you used based on the Locale of the server etc. BUT it will also use the LANGUAGE of the currently connected user and other such things which WILL change depending on who is logged on, or what the setting of the server is - which may change in the future at an upgrade etc.
You are therefore much better off using an UNambiguous date format for strings. That needs to be one of:
'yymmdd' - Note: NO punctuation - thus 'yyyy-mm-dd' is not treated as unambiguous
'yyyymmdd'
'yyyy-mm-ddThh:mm:ss.sss' - this does require the punctuation as shown.
You can truncate the time e.g.
'yyyy-mm-ddThh:mm:ss' (This is the ISO style format)
or
'yyyy-mm-ddThh:mm'
Or in SQL2012 you can use
SELECT DateFromParts(2013, 4, 1) -- Parameters are Year, month, Day
Other possibility is to provide a HINT to SQL as to the format of the date:
SET DATEFORMAT dmy
to tell it that your dates are 'dd/mm/yyyy' sequence
Or you can use:
SELECT CONVERT(datetime, '01/04/2013 03:24:50', *999*)
where "999" is a specific date format value (you'll have to look that up in the DOCs for the date format you are using, if it is supported)
Could you please provide the code you are using? Also what is the data type of the column?
Kristen has provided date basics that are good to remember.
Please help me to find the exact data
which shows the error :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Date field having record like '01/04/2013 03:24:50'