How to convert varchar to datetime in Sql server database

parvez khan

SELECT [BillNo],[BillDate],[Pts_Name],[Drs_Name],[Medi_Name],[BatchNo],[ExpDt],[MRP]

,[Qty],[Amount] FROM [MEDIDATANEW].[dbo].[CustBill]where (convert (datetime ,convert(datetime,convert(varchar,BillDate,103),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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nigel Fernandes9y ago
Accepted answer
Can you post some sample data from the varchar column you wish to convert to datetime.
+1
Jes Sie9y ago
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
0
Ramesh Barik9y ago
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.
+1
Bryian Tan9y ago
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)))
0
parvez khan9y ago
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

0
Bryian Tan9y ago
It ran on MSSQL 2012, what the error message you getting?
0