请问SQL server 2000 里的 datetime field 的问题

DRAGONISM

我已经在我的电脑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 呢?

谢谢.

goatstudio

没用的, 你要自行在 sql syntax 里用 CONVERT.

看到你另外一则帖的示范，我已明白了。就是我放的日期是什么格式，就放什么代码进去，如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 ]

DRAGONISM

1. 
   
2. .....
   
3. dim sSQL as String
   
4. dim dsDataSet as DataSet
   
5. dim startDate as Date
   
6. dim endDate as Date
   
7. 
   
8. startDate = StartDateOfMonth(2005, 3) 'return 01 March 2005
   
9. endDate = EndDateOfMonth(2005, 4) 'return 31 March 2005
   
10. 
    
11. sSQL = "select oid, description from tblA " & _
    
12.        "where oDate between convert(datetime, '" & startDate & "', 103) " & _
    
13.        "and convert(datetime, '" & endDate & "', 103)"
    
14. 
    
15. dsDataSet = SqlRetrieveData(sSQL, "datasetA" ,gSQLConn)
    
16. .....

复制代码

1. 
   
2. Public Function SqlRetrieveData(ByVal sSQL As String, ByVal sTable As String, ByVal sConnectStr As String) As DataSet
   
3.     Dim myConnection As New SqlConnection
   
4.     Dim myDataAdapter As New SqlDataAdapter
   
5.     Dim dsDataSet As New DataSet
   
6. 
   
7.     Try
   
8.         'Initiate a Sql connection
   
9.         myConnection.ConnectionString = sConnectStr
   
10.         myConnection.Open()
    
11. 
    
12.         'Initiate SqlDataAdapter
    
13.         myDataAdapter.SelectCommand = New SqlCommand(sSQL, myConnection)
    
14.         myDataAdapter.Fill(dsDataSet, sTable)
    
15. 
    
16.     Catch ex As Exception
    
17.         MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
18.     End Try
    
19. 
    
20.     'Close connection
    
21.     myConnection.Close()
    
22.     myConnection = Nothing
    
23. 
    
24.     Return dsDataSet
    
25. End Function
    
26. 
    
27. 
    
28. 
    
29. Public Function EndDateOfMonth(ByVal iyear As Integer, ByVal iMonth As Integer) As Date
    
30.     Dim dteDate As Date
    
31.     dteDate = DateSerial(iyear, iMonth, 0)
    
32. 
    
33.     Return dteDate
    
34. End Function
    
35. 
    
36. Public Function StartDateOfMonth(ByVal iyear As Integer, ByVal iMonth As Integer) As Date
    
37.     Dim dteDate As Date
    
38.     dteDate = DateSerial(iyear, iMonth, 1)
    
39. 
    
40.     Return dteDate
    
41. End Function

复制代码

[ Last edited by DRAGONISM on 17-3-2005 at 03:44 PM ]

extrem23

个人通常在Front End,先Format成‘2005-12-15'，
这样的格式能确保任何SQL Server Datetime都能接受。

DRAGONISM

小弟有个疑问，以上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.

我知道有一个解决方法是:

1. .....
   
2. dim sSQL as String
   
3. dim dsDataSet as DataSet
   
4. dim startDate as Date
   
5. dim endDate as Date
   
6. 
   
7. startDate = StartDateOfMonth(2005, 3) 'return 01 March 2005
   
8. endDate = EndDateOfMonth(2005, 4) 'return 31 March 2005
   
9. 
   
10. sSQL = "select oid, description from tblA " & _
    
11.        "where oDate between convert(datetime, '" & Format(startDate, "dd/MM/yyyy") & "', 103) " & _
    
12.        "and convert(datetime, '" & Format(endDate, "dd/MM/yyyy") & "', 103)"
    
13. 
    
14. dsDataSet = SqlRetrieveData(sSQL, "datasetA" ,gSQLConn)
    
15. .....

复制代码

可是我知道应该还有更简单的方法去写这个SQL，希望有网友可以教一教我。谢谢

注：我用VB.NET

[ Last edited by DRAGONISM on 17-3-2005 at 04:03 PM ]

cswah_yy

用 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 ]

astral

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的问题