How to convert varchar to datetime in Sql server database

Question

SELECT [BillNo][BillDate][Pts_Name][Drs_Name][Medi_Name][BatchNo][ExpDt][MRP] [Qty][Amount] FROM [MEDIDATANEW].[dbo].[CustBill]where (convert (datetime convert(datetimeconvert(varcharBillDate103)103))between '2014-03-12' and '2014-03-23') This script run in sqlserver 2008 But Not Run in sqlserver 2008R2 and upper version. plz Help me

Nigel Fernandes · Answer

Can you post some sample data from the varchar column you wish to convert to datetime.

Ramesh Barik · Answer

SELECT [BillNo][BillDate][Pts_Name][Drs_Name][Medi_Name][BatchNo][ExpDt][MRP] [Qty][Amount] FROM [MEDIDATANEW].[dbo].[CustBill] where BillDate between convert(DATETIME '2014-03-12' 102) AND convert(DATETIME '2014-03-23' 102) Try this may it help u.

Hold On · Answer

Maybe this will help you Make a stored procedure like below: SELECT h.TaxInvoiceNumber h.TransactionDate h.BookNumber c.CustomerName c.Province sum (d.ConvertedNetPremium) as NetPremium sum (d.ConvertedVAT) as VAT sum (d.ConvertedRegistryFee) as RegistryFee SUM (d.ConvertedNetPremium + d.ConvertedVAT + d.ConvertedRegistryFee) as TOTAL FROM dbo.CashierSalesHeader h INNER JOIN dbo.CashierSalesDetails d ON h.TransactionNumber = d.TransactionNumber INNER JOIN dbo.tblCustomers c ON d.CustomerNo = c.CustomerNo WHERE (h.TransactionDate BETWEEN @DateStart AND @DateEnd) GROUP BY h.TaxInvoiceNumber h.TransactionDate h.BookNumber c.CustomerName c.Province

Bryian Tan · Answer

Msg 242 Level 16 State 3 Line 2 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value That sound like data issue. Do you have some sample data for me to look at? Do you get any error running the following query? SELECT convert ( varchar GETDATE()103) ( convert (datetime  convert (datetime convert ( varchar GETDATE()103)103)))

parvez khan · Answer

Error Msg Msg 242 Level 16 State 3 Line 2 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Bryian Tan · Answer

It ran on MSSQL 2012 what the error message you getting?