Insert/update datetime from VB6 to Sql Server

Jitendra Shekhawat

9y1.3kVisual Basic .NET

In MS ACCESS

itmarr(0, 0) = "bill_date"

itmarr(1, 0) = TxtDate.Text

IN SQL SERVER

itmarr(0, 0) = "bill_date"

itmarr(1, 0) = Format(TxtDate.Text, "dd MMM yyyy")

ERROR SHOW IN SQL SERVER]

the conversion of a varchar data type to a datetime data type resulted in an out-of-range value

5 Replies

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

Rafnas T P9y ago
Accepted answer
in sql do like this
itmarr(1, 0) = Format(TxtDate.Text, "yyyy-MM-dd")
or
itmarr(1, 0) =Convert.ToDateTime(TxtDate.Text).ToString("yyyy-MM-dd");
Jitendra Shekhawat9y ago
how to change this querry in sql server 2012

"SELECT b.trn_sno, b.bill_date, b.bill_bno, b.pt_name, b.age, b.sex, iif(d.doct_nm Is Null,'',d.doct_nm), " & _
"b.amount, iif(b.chk_free=1,b.amount,0), iif(b.chk_free=1,0,b.amount), iif(iif(b.app_ref is null,0,b.app_ref)=1,iif(iif(b.rec_ref is null,0,b.rec_ref)=1,'Refunded','Approve for Refund'),'') FROM rec_all as b LEFT JOIN doctors as d ON b.doct_id=d.doct_id WHERE " & txtfld & " and " & _
"b.rw=0 and b.co_code='" & CoCode & "' ORDER BY b.rec_ref, b.bill_date, b.trn_sno"
Nitin Sontakke9y ago
Just to clarify, there is NO format of date stored. Dates are stored as numbers. Date format is a just visual representation and can be modified as per user requirement by applying appropriate format in SELECT statement.

For example, execute following in SQL Server Management Studio:

select convert(varchar(50), getdate(), 100) format1
,convert(varchar(50), getdate(), 101) format2
,convert(varchar(50), getdate(), 102) format3
,convert(varchar(50), getdate(), 106) format4
Rafnas T P9y ago
in Sql server it saves in the format of yyyy-MM-dd.
if you want you can show it in front end by converting what ever format you want after retriving data from sql server
Jitendra Shekhawat9y ago
ERROR SHOW IN SQL SERVER]
the conversion of a varchar data type to a datetime data type resulted in an out-of-range value

And i want to Date format :- ("dd MMM yyyy")