dbtSQLSERVER = 0
dbtACCESS = 1
dbtMySQL = 2
dbtORACLE = 3
End Enum
Public Function CSqlDate(ByVal vDate As Variant, Optional dbType As DBTypeConstant = dbtORACLE) As String
'Remove all invalid characters
vDate = Trim(CStr(vDate))
vDate = Replace(vDate, "#", "")
vDate = Replace(vDate, "'", "")
vDate = Replace(vDate, Chr(34), "")
'--------------------------------------
'Convert the Date to a Double Precision
' for international compatability
'--------------------------------------
CSqlDate = ""
'First see in what format the data came
' Validate parameter
If Not IsDate(vDate) Or IsNull(vDate) Then
'Maybe it is a number
If IsNumeric(vDate) Then
vDate = CDate(vDate)
End If
If Not IsDate(vDate) Then
'Still not a date
Exit Function
End If
End If
Select Case dbType
Case dbtSQLSERVER, dbtORACLE
CSqlDate = Format(vDate, "mm\/dd\/yyyy")
CSqlDate = "'" & CSqlDate & "'"
Case dbtMySQL
CSqlDate = Format(vDate, "yyyy-mm-dd")
CSqlDate = "'" & CSqlDate & "'"
Case dbtACCESS
CSqlDate = FormatDateTime(vDate, vbLongDate)
'Format by Regional Settings
CSqlDate = "#" & CSqlDate & "#"
End Select
End Function
Public Function CSqlDateTime(ByVal vDate As Variant, Optional dbType As DBTypeConstant = dbtORACLE) As String
'Remove all invalid characters
vDate = Trim(CStr(vDate))
vDate = Replace(vDate, "#", "")
vDate = Replace(vDate, "'", "")
vDate = Replace(vDate, Chr(34), "")
'--------------------------------------
'Convert the Date to a Double Precision
' for international compatability
'--------------------------------------
CSqlDateTime = ""
'First see in what format the data came
' Validate parameter
If Not IsDate(vDate) Or IsNull(vDate) Then
'Maybe it is a number
If IsNumeric(vDate) Then
vDate = CDate(vDate)
End If
If Not IsDate(vDate) Then
'Still not a date
Exit Function
End If
End If
Select Case dbType
Case dbtSQLSERVER, dbtMySQL, dbtORACLE
'Format is MM/DD/??YY HH:MM:SS
CSqlDateTime = Format(vDate, "mm\/dd\/yyyy hh\:mm\:ss")
CSqlDateTime = "'" & CSqlDateTime & "'"
Case dbtACCESS
CSqlDateTime = FormatDateTime(vDate, vbGeneralDate)
'Format by Regional Settings
CSqlDateTime = "#" & CSqlDateTime & "#"
End Select
End Function
Public Function CSqlTime(ByVal vDate As Variant, Optional dbType As DBTypeConstant = dbtORACLE) As String
'Remove all invalid characters
vDate = Trim(CStr(vDate))
vDate = Replace(vDate, "#", "")
vDate = Replace(vDate, "'", "")
vDate = Replace(vDate, Chr(34), "")
'--------------------------------------
'Convert the Date to a Double Precision
' for international compatability
'--------------------------------------
CSqlTime = ""
'First see in what format the data came
' Validate parameter
If Not IsDate(vDate) Or IsNull(vDate) Then
'Maybe it is a number
If IsNumeric(vDate) Then
vDate = CDate(vDate)
End If
If Not IsDate(vDate) Then
'Still not a date
Exit Function
End If
End If
CSqlTime = Format(vDate, "hh\:mm\:ss")
Select Case dbType
Case dbtSQLSERVER, dbtMySQL, dbtORACLE
'Format is MM/DD/??YY HH:MM:SS
CSqlTime = "'" & CSqlTime & "'"
Case dbtACCESS
'Format by Regional Settings
CSqlTime = "#" & CSqlTime & "#"
End Select
End Function
Sorry neh ikutan nimbrung, masih ngebahas masalah konversi date di SQL Server,oke rekan2, memang tdk akan ada error jika metode insert kita di vb menggunakan format yyyy-mm-dd, nah yang bermasalah di SQL Servernya sendiri, sekarang coba asumsikan kita insert tanggal dengan format indonesia (tapi regional settingnya adalah english dengan format mm/dd/yyyy).dimana textboxnya sudah kt format misal : Format(Tdate.text,"yyyy-mm-dd"), nah sekarang kita entry tanggal 13 bulan 09 tahun 2005, ato entry nya seperti ini 13/09/2005, kita save, tetap bisa ke insert ato kesimpan ke sql server.Nah sekarang coba rekan2 masuk ke sql server, cari tabel dimana field datenya sudah kita insert dari VB, kemudian kita open tabelnya, datanya akan menjadi seperti ini 09/05/2013Note : ini kasusku yang sampe sekarang jadi PR nih, kasus ini dimana regional setting o/s nya di set english dengan format mm/dd/yyyy, karena usernya gak mau format regional settingnya di ubah ke format indonesia, dikarenakan excelnya ada formula yang mengharuskan regional setting diset english dengan format mm/dd/yyyy.Buat rekan2 viva programmer VB
herlisa eka putri <[EMAIL PROTECTED]> wrote:wah..makasi..udah bisa..saya pake format yyyy-mm-ddsyukur g masalah lagi...oh ya..satu lagi..kalau kita memasukkan nilai melalui texbox dgn nilai 3:00:00(format jam)dan didatabasenya pake tipe data date...setelah dimasukkan..dan disimpan didatabase...eh jadinya 1/1/1990 3:00:00(tanggal dan waktunya)gmn yah kalo cuma mo nyimpan dengan nilai jamnya saja di databasenya....sebelumnya..thx lagi yahcoba mbak herli (ato eka ato putri nih :P) masukin dalam medium format
ato long format, atau yyyy-mm-dd biasanya ga ada masalah.
u. format indonesia, itu cukup di format waktu proses penampilan aja.
rgds
[wwn]
On 9/8/05, herlisa eka putri < [EMAIL PROTECTED]> wrote:
> saya ada instruksi untuk memasukkan nilai denagn format date ke data
> base(SQL Server 2000)
> regional yang saya gunakan di komputer saya =Indoensia
> nilai yang saya masukkan dalam sql insert nya nilainya
> misal=25/07/2005(format indonesia)
> tapi saat di execute ada pesan error "convertion datetimenya out-of-range"
> namun klau saya masukkan nilai 25/07/2005 langsung ke table malalui SQL
> Servernya= tdk ada masalah...
> sepertinya pada query insert itu data format indonesia tadi diganti kan...
> bagaimna yah agar dari VB mengirim nilai date teteap adalam format indonesia
> dan diterima oleh SQL Server dalam format indonesia juga...
> Mohon pencerahannya..
> Thx B4
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> Untuk keluar dari millis ini, kirim email kosong ke:
> [EMAIL PROTECTED]
>
>
>
>
> ________________________________
> YAHOO! GROUPS LINKS
>
> Visit your group "Programmer-VB" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
> ________________________________
>
>
>__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.
Untuk keluar dari millis ini, kirim email kosong ke:
[EMAIL PROTECTED]
SPONSORED LINKS
Programmer Indonesia Basic programming language Computer programming languages Programming languages Java programming language
YAHOO! GROUPS LINKS
- Visit your group "Programmer-VB" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
Untuk keluar dari millis ini, kirim email kosong ke:
[EMAIL PROTECTED]
| Programmer | Indonesia | Basic programming language |
| Computer programming languages | Programming languages | Java programming language |
YAHOO! GROUPS LINKS
- Visit your group "Programmer-VB" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
