- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 请教关于mysql Unix 时间戳的转换问题 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=50654)
-- 发布时间：2014/5/10 17:27:00
-- 请教关于mysql Unix 时间戳的转换问题
如题。。百度上看到是这样的。select from_unixtime(1218290027)，但是我要下载订单数据下来。要转换成yyyy-mm-dd，
这样代码要如何写。表格为orderlist，列是AddTime。。请教大师们
[此贴子已经被作者于2014-5-10 17:28:06编辑过]
-- 发布时间：2014/5/10 17:45:00
--
Dim dtStart As DateTime = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970,1,1))
Dim dtResult As DateTime =dtStart.AddSeconds(1218290027)
MessageBox.show(dtResult)
-- 发布时间：2014/5/10 18:18:00
--
不理解如何套进去。。AddTime是mysql数据库unix时间戳， 日期是狐表里datatable yyyy-mm-dd格式的列。如何在下载订单的时候让日期转化，不然就会出现格式错误提示。
Dim cmd As New SQLCommand
Dim dt As DataTable
Dim sCols() As String = {"AddTime"}
Dim dCols() As String = {"日期"}
cmd.C
cmd.CommandText = "SELECT * From orderson a INNER JOIN orderlist b ON a.OrderNumber = b.OrderNumber Order By orderson.WeiZhi "
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
If DataTables("订单表").Find("编号 = \'" & dr("OrderNumber") & "\' And ID =\'" & dr("ID") & "\'") Is Nothing Then
Dim nr As DataRow = DataTables("小包发货单").AddNew()
For i As Integer =0 To sCols.Length -1
nr(dCols(i)) = dr(sCols(i))
Next
End If
Next
[此贴子已经被作者于2014-5-10 18:35:09编辑过]
-- 发布时间：2014/5/10 18:43:00
--
Dim d1 As Date = #1/1/1970 08:00:00#
Dim dr1,dr2,nr As DataRow
Dim cmd As New SQLCommand
Dim dt1,dt2 As DataTable
Dim sCols() As String = {"AddTime"}
Dim dCols() As String = {"日期"}
cmd.C
cmd.CommandText = "SELECT * From orderson a INNER JOIN orderlist b ON a.OrderNumber = b.OrderNumber Order By orderson.WeiZhi "
dt1 = cmd.ExecuteReader()
dt2 = DataTables("订单表")
dt2.StopRedraw()
For Each dr1 In dt.DataRows
dr2= dt2.Find("编号 = \'" & dr("OrderNumber") & "\' And ID =\'" & dr("ID") & "\'")
If dr2 Is Nothing Then
nr = DataTables("小包发货单").AddNew()
nr("日期") = d1.AddSeconds(dr("AddTime"))
End If
Next
dt2.ResumeRedraw()
-- 发布时间：2014/5/10 20:07:00
--
能转换日期，时间都是0:00:00。并且提示字符串类型转换成date 转换无效。
此主题相关图片如下：qq截图20140510200309.jpg
-- 发布时间：2014/5/10 20:31:00
--
Dim d1 As Date = #1/1/1970 08:00:00#
Dim dt1,dt2 As DataTable
Dim dr1,dr2,nr As DataRow
Dim sql as string ="SELECT * From orderson a INNER JOIN orderlist b ON "
sql+ = "a.OrderNumber = b.OrderNumber Order By orderson.WeiZhi"
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText =sql
dt1 = cmd.ExecuteReader()
dt2 = DataTables("订单表")
dt2.StopRedraw()
For Each dr1 In dt.DataRows
dr2= dt2.Find( "编号 = \'" & dr("OrderNumber") & "\' And ID =\'" & dr("ID") & "\'")
If dr2 Is Nothing Then
nr = DataTables("小包发货单").AddNew()
nr("日期") = d1.AddSeconds(Val(dr("AddTime")))
End If
Next
dt2.ResumeRedraw()
[此贴子已经被作者于2014-5-10 20:39:14编辑过]
-- 发布时间：2014/5/10 20:45:00
--
还是同样的问题。没解决。同样提示字符串类型转换为Date失效
Dim d1 As Date = #1/1/1970 08:00:00#
Dim cmd As New SQLCommand
Dim dt As DataTable
Dim sCols() As String = {"AddTime"}
Dim dCols() As String = {"日期"}
cmd.C
cmd.CommandText = "SELECT * From (orderson INNER JOIN orderlist ON orderson.OrderNumber = orderlist.OrderNumber) INNER JOIN vipuser ON orderlist.UserName = vipuser.UserName Order By orderson.WeiZhi "
dt = cmd.ExecuteReader()
DataTables("小包发货单").StopRedraw()
For Each dr As DataRow In dt.DataRows
If DataTables("小包发货单").Find("编号 = \'" & dr("OrderNumber") & "\' And ID =\'" & dr("ID") & "\'") Is Nothing Then
Dim nr As DataRow = DataTables("小包发货单").AddNew()
nr("日期") = d1.AddSeconds(Val(dr("AddTime")))
For i As Integer =0 To sCols.Length -1
nr(dCols(i)) = dr(sCols(i))
Next
End If
Next
DataTables("小包发货单").ResumeRedraw()
[此贴子已经被作者于2014-5-10 20:45:54编辑过]
-- 发布时间：2014/5/10 21:12:00
--
例子說話
-- 发布时间：2014/5/10 21:22:00
--
以下是引用jimmyhwang在2014-5-10 20:45:00的发言：
还是同样的问题。没解决。同样提示字符串类型转换为Date失效
Dim d1 As Date = #1/1/1970 08:00:00#
Dim cmd As New SQLCommand
Dim dt As DataTable
Dim sCols() As String = {"AddTime"}
Dim dCols() As String = {"日期"}
cmd.C
cmd.CommandText = "SELECT * From (orderson INNER JOIN orderlist ON orderson.OrderNumber = orderlist.OrderNumber) INNER JOIN vipuser ON orderlist.UserName = vipuser.UserName Order By orderson.WeiZhi "
dt = cmd.ExecuteReader()
DataTables("小包发货单").StopRedraw()
For Each dr As DataRow In dt.DataRows
If DataTables("小包发货单").Find("编号 = \'" & dr("OrderNumber") & "\' And ID =\'" & dr("ID") & "\'") Is Nothing Then
Dim nr As DataRow = DataTables("小包发货单").AddNew()
nr("日期") = d1.AddSeconds(Val(dr("AddTime")))
For i As Integer =0 To sCols.Length -1
nr(dCols(i)) = dr(sCols(i)) \'这里好像不妥 翻过来就是 nr("日期")=dr("AddTime"),这两个的类型不同
Next
End If
Next
DataTables("小包发货单").ResumeRedraw()
[此贴子已经被作者于2014-5-10 20:45:54编辑过]
-- 发布时间：2014/5/10 22:03:00
--
问题解决了。。因为addnew在前，前面列了日期的对接，所以执行Addnew时就会出错。后面的nr（“日期”）等于是重新赋值了。只要把前面的对接删除了就没问题了。非常感谢~还有Bin, 其实两种方案都是一样的可以用。:)