查看: 1216|回复: 7
|
请问SQL server 2000 里的 datetime field 的问题
[复制链接]
|
|
我已经在我的电脑set 了date的regional setting 去dd/MM/yyyy , 然后我用query
analyzer execute 以下的sql statement:
insert into tblA values('31/12/05')
出现下面的error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
然后我set 了date的regional setting 去MM/dd/yyyy, 也一样是出现同样的error.
是不是sql statement 的date format一定是dd/MM/yyyy, 而不需要理会电脑的regional
settings 呢?
谢谢. |
|
|
|
|
|
|
|
发表于 1-2-2005 11:42 PM
|
显示全部楼层
没用的, 你要自行在 sql syntax 里用 CONVERT. |
|
|
|
|
|
|
|
发表于 2-2-2005 07:57 AM
|
显示全部楼层
看到你另外一则帖的示范,我已明白了。就是我放的日期是什么格式,就放什么代码进去,如dd/mm/yyyy就放103。谢谢。
CONVERT(DATETIME,MyDateValue,103)
Values for the 3rd parameter.
1 mm/dd/yy
2 yy.mm.dd
3 dd/mm/yy
4 dd.mm.yy
5 dd-mm-yy
6 dd mon yy
7 mon dd, yy
10 mm-dd-yy
11 yy/mm/dd
12 yymmdd
Add 100 to a style value to get a four-place year that includes the century (yyyy).
[ Last edited by maybird_2 on 2-2-2005 at 08:04 AM ] |
|
|
|
|
|
|
|
楼主 |
发表于 17-3-2005 03:37 PM
|
显示全部楼层
- .....
- dim sSQL as String
- dim dsDataSet as DataSet
- dim startDate as Date
- dim endDate as Date
- startDate = StartDateOfMonth(2005, 3) 'return 01 March 2005
- endDate = EndDateOfMonth(2005, 4) 'return 31 March 2005
- sSQL = "select oid, description from tblA " & _
- "where oDate between convert(datetime, '" & startDate & "', 103) " & _
- "and convert(datetime, '" & endDate & "', 103)"
- dsDataSet = SqlRetrieveData(sSQL, "datasetA" ,gSQLConn)
- .....
复制代码
- Public Function SqlRetrieveData(ByVal sSQL As String, ByVal sTable As String, ByVal sConnectStr As String) As DataSet
- Dim myConnection As New SqlConnection
- Dim myDataAdapter As New SqlDataAdapter
- Dim dsDataSet As New DataSet
- Try
- 'Initiate a Sql connection
- myConnection.ConnectionString = sConnectStr
- myConnection.Open()
- 'Initiate SqlDataAdapter
- myDataAdapter.SelectCommand = New SqlCommand(sSQL, myConnection)
- myDataAdapter.Fill(dsDataSet, sTable)
- Catch ex As Exception
- MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error)
- End Try
- 'Close connection
- myConnection.Close()
- myConnection = Nothing
- Return dsDataSet
- End Function
- Public Function EndDateOfMonth(ByVal iyear As Integer, ByVal iMonth As Integer) As Date
- Dim dteDate As Date
- dteDate = DateSerial(iyear, iMonth, 0)
- Return dteDate
- End Function
- Public Function StartDateOfMonth(ByVal iyear As Integer, ByVal iMonth As Integer) As Date
- Dim dteDate As Date
- dteDate = DateSerial(iyear, iMonth, 1)
- Return dteDate
- End Function
复制代码
[ Last edited by DRAGONISM on 17-3-2005 at 03:44 PM ] |
|
|
|
|
|
|
|
发表于 17-3-2005 03:55 PM
|
显示全部楼层
个人通常在Front End,先Format成‘2005-12-15',
这样的格式能确保任何SQL Server Datetime都能接受。 |
|
|
|
|
|
|
|
楼主 |
发表于 17-3-2005 03:58 PM
|
显示全部楼层
小弟有个疑问,以上Part 1的coding在Regional Setting dd/MM/yyyy 的电脑跑没有问题。
可是在Regional Setting MM/dd/yyyy 的电脑跑就出现问题于要执行SQL statement的时候,error是
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.
我知道有一个解决方法是:
- .....
- dim sSQL as String
- dim dsDataSet as DataSet
- dim startDate as Date
- dim endDate as Date
- startDate = StartDateOfMonth(2005, 3) 'return 01 March 2005
- endDate = EndDateOfMonth(2005, 4) 'return 31 March 2005
- sSQL = "select oid, description from tblA " & _
- "where oDate between convert(datetime, '" & Format(startDate, "dd/MM/yyyy") & "', 103) " & _
- "and convert(datetime, '" & Format(endDate, "dd/MM/yyyy") & "', 103)"
- dsDataSet = SqlRetrieveData(sSQL, "datasetA" ,gSQLConn)
- .....
复制代码
可是我知道应该还有更简单的方法去写这个SQL,希望有网友可以教一教我。谢谢
注:我用VB.NET
[ Last edited by DRAGONISM on 17-3-2005 at 04:03 PM ] |
|
|
|
|
|
|
|
发表于 18-3-2005 12:52 AM
|
显示全部楼层
用 Microsoft SQL server 吗?
试试这个,希望可以帮到你!
Select <column_name>
From <table_name>
Where <column_name> = cast('2005-12-31' as datetime);
[ Last edited by cswah_yy on 18-3-2005 at 12:54 AM ] |
|
|
|
|
|
|
|
发表于 18-3-2005 06:25 AM
|
显示全部楼层
DRAGONISM 于 17-3-2005 03:58 PM 说 :
小弟有个疑问,以上Part 1的coding在Regional Setting dd/MM/yyyy 的电脑跑没有问题。
可是在Regional Setting MM/dd/yyyy 的电脑跑就出现问题于要执行SQL statement的时候,error是
The conversion of a c ...
用vb.net? 那就让.net 去处理....
eg:
myDataAdapter.SelectCommand.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value = aDateTimeTypeVariable
*aDateTimeTypeVariable 是 datetime type, 不用自己convert去string type. 所以你没有format的问题 |
|
|
|
|
|
|
| |
本周最热论坛帖子
|