| Author |
 Topic  |
|
asm
Posting Yak Master
134 Posts |
 Posted - 11/15/2005 : 01:19:02
|
table datatype is smalldatetime
asm |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 01:54:49
|
How do you pass values to date column?
Give example
Madhivanan
Failing to plan is Planning to fail |
|
|
|
asm
Posting Yak Master
134 Posts |
Posted - 11/15/2005 : 03:28:59
|
At the time of form load txtdate.text = date ' system current date
I give below the save button procedure.
Private Sub CmdSave_Click()
If addflag = True Then
Dim Memp_no, Memp_no1, memp_no2 As Double
Dim Mcode1, Mcode, memp_str As String
Mcode = "SI"
With Rec_Voucher
.MoveFirst
Memp_no = Rec_Voucher!si
Memp_no1 = Memp_no + 1
memp_no2 = Trim(Str(Memp_no1))
memp_str = padstring(memp_no2, 7, "R", "0")
Mcode1 = Mcode + memp_str
Rec_Voucher!si = Memp_no1
Rec_Voucher.Update
End With
'STOCK TABLE
With Rec_Stock
.AddNew
Rec_Stock!document = UCase(Mcode1)
Rec_Stock!Lcode = TxtLcode.Text
Rec_Stock!pcode = TxtPcode.Text
If TxtDate.Text = "" Then
Rec_Stock!doc_date = Null
Else
Rec_Stock!doc_date = TxtDate.Text
End If
Rec_Stock!QTY = TxtQty.Text
Rec_Stock!Type = UCase(TxtOld.Text)
Rec_Stock!mov = "I"
Rec_Stock!vtype = "SI"
Rec_Stock.Update
End With
End If
TxtDocument.Text = Rec_Stock!document
addflag = False
CmdSave.Enabled = False
Call cmd_true1
Rec_Stock.Requery
Rec_StockSI.Requery
End Sub
asm |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 03:32:24
|
Are the dates storing correctly in the table?
I prefer this
Rec_Stock!doc_date = Format(TxtDate.Text,"yyyymmdd "HH:MM:SS")
Madhivanan
Failing to plan is Planning to fail |
|
|
|
asm
Posting Yak Master
134 Posts |
Posted - 11/15/2005 : 03:56:51
|
Yes, like this : 2005-09-29 00:00:00
If i give date in mmddyyyy format result are correct but if i give date in ddmmyyyy format as then flash error.
asm
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 04:14:29
|
>>If i give date in mmddyyyy format result are correct but if i give date in ddmmyyyy format as then flash error.
Thats why I told you to use Universal format 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
asm
Posting Yak Master
134 Posts |
Posted - 11/15/2005 : 04:31:31
|
BUT THE USER WANT THE DD/MM/YYYY FORMAT.
ASM
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 11/15/2005 : 04:48:24
|
see... this is where i lost a few inches of my nerves
once you get proper dates into your db you think... phewww... that's over.
and then some silly client wants to see his dates that are already stored in db in proper format.
stupid clients...
well each session has client locale in it so you can format it with that.
data stored in db should be formatted at the client because that's not server's job.
the format of the dates you see in SQL server is irrelevat as it depends on your servers regional settings.
in essence dates are stored as 2 int's one for date part and one for time part
Go with the flow & have fun! Else fight the flow  |
|
|
|
asm
Posting Yak Master
134 Posts |
Posted - 11/15/2005 : 06:07:33
|
client not bother with how the sql server store the date
They only want - he give the input date in dd/mm/yyyy format.
(VB6) now i try to convert the ddmmyyyy date for to yyyymmdd format
vb6 flash error type mismatch..
code
----
Fdate = Format(TxtFromDate.Text, "yyyymmdd")
Tdate = Format(TxtToDate.Text, "yyyymmdd")
SqlString = "select * from Journalreg where docdate >='" & Fdate & "' and docdate<='" & Tdate & "' ORDER BY VOUCHER"
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/15/2005 : 06:19:32
|
"yyyymmdd
yyyy/mm/dd
yyyy-mm-dd"
We only allow the first one (and yyyy-mm-ddThh:mm:ss.mmm). Here's output from one of our client's SQL boxes:
SELECT GetDate()
GO
--> 2005-11-15 11:19:01.447
SELECT CONVERT(datetime, '20051115')
GO
--> 2005-11-15 00:00:00.000
SELECT CONVERT(datetime, '2005-11-15')
GO
--> 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.
SELECT CONVERT(datetime, '2005/11/15')
GO
--> 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.
SELECT CONVERT(datetime, '2005-11-15T01:02:03.400')
GO
--> 2005-11-15 01:02:03.400
Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 06:32:08
|
Kris, what is your server's Date setting?
I get these
SELECT GetDate()
GO
--> 2005-11-15 17:12:11.997
SELECT CONVERT(datetime, '20051115')
GO
--> 2005-11-15 00:00:00.000
SELECT CONVERT(datetime, '2005-11-15')
GO
--> 2005-11-15 00:00:00.000
SELECT CONVERT(datetime, '2005/11/15')
GO
--> 2005-11-15 00:00:00.000
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/15/2005 : 06:42:07
|
No idea what they have set the server too, all I was meaning was that yyyy-mm-dd and yyyy/mm/dd are less than ideal to standardise on.
Kristen |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/15/2005 : 08:44:13
|
quote:
In VB6, if you assign the value to date variable as '05/08/2005' assuming that it is 5th August,2005 and if you dont format it using universal format then it will be stored in SQL Server as 8th May, 2005
Yes. IN VB6. This has *nothing* to do with SQL Server.
Forget about sql server. Shut down the box, unplug it, put it in the closet. Your first step is to figure out how to set date variables AT THE CLIENT. If you can't do this, then you have no business passing in these values to SQL Server hoping that it will intepret them correctly. If you can't set the date variable "x" equal to the proper date that you want to store, then how can you expect to pass a date to SQL Server and have SQL store it properly?
If you can set a date variable to the proper value at the client and if you use parameters, then the entire thing is moot at sql server -- there's no date settings, formats, conversions, etc, to worry about. If you have trouble with this, then it is an issue with VB (or whatever client language you are using) -- google for a VB forum and work it out there. Once you have that solved, and only then, should you grab your SQL Box, plug it back in, and then worry about how to upload those dates to the server.
And, of course, when you do that, you use a parameter -- you do NOT concatenate a string together and execute it like you are showing. Then formatting is NOT an issue.
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 08:54:57
|
Good Points. I need to think of that
Madhivanan
Failing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/15/2005 : 09:17:56
|
| And, of course, it is the same the other way around -- once those dates are in the SQL box, when you return them to the client, you return the date VALUES. Never do a CONVERT around a date and return a VARCHAR to a client that is perfectly capable of doing all the date formatting itself. This way, 10 different clients can connect to your database, they can all pull dates out of the database, and each one can format those dates any way they want regardless of any internal date settings in SQL. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2005 : 09:29:53
|
Yes. Retreiving date from SQL Server doesnt make any problem as it is stored as yyyy-mm-dd HH:MM:SS:MS format so that all clients with different local setting can very well handle it without any conversion. But the problem is to send Date to SQL Server from Client
Madhivanan
Failing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/15/2005 : 10:17:41
|
>>Retreiving date from SQL Server doesnt make any problem as it is stored as yyyy-mm-dd HH:MM:SS:MS format so that all clients with different local setting can very well handle it without any conversion
No ... it is not stored in ANY format. It is stored as an internal representation of a date VALUE. This is a very important concept to understand and goes a long way towards helping people to avoid spending days on issues like the ones presented in this thread.
>>But the problem is to send Date to SQL Server from Client
Again ... no. The only problem is when you try to concatenate a SQL statement with string representations of dates, and you don't use parameters or actual date datatypes at the client. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/15/2005 : 11:05:50
|
If you are using dynamic SQL in the application (bad as that may be!) then you are going to need to pass the date as a string aren't you?
SET strSQL = "SELECT * FROM MyTable WHERE MyColumn = " & ... MyVBDateGoesHere
Kristen |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/15/2005 : 11:17:08
|
quote:
Originally posted by Kristen
If you are using dynamic SQL in the application (bad as that may be!) then you are going to need to pass the date as a string aren't you?
SET strSQL = "SELECT * FROM MyTable WHERE MyColumn = " & ... MyVBDateGoesHere
Kristen
If you need to use dynamic sql, except for very rare cases (i.e., a complicated report that needs to build a complete SQL statement from scratch) you can and should still use parameters. Dynamic SQL and parameters are NOT mutually exclusive.
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/15/2005 : 11:59:36
|
sp_ExecuteSQL type thingie you mean? Or is there some slinky way to make a "SQL String" by appending variables (like using AppendParameter for SProcs in ADO stuff) that takes care of the data types / formatting?
I'm thinking of an environment where SProcs aren't used <shudder!>
What about where the language doesn't "type" the variables - VBS doesn't provide a native Date data type for variables, does it?
(Although our stuff is all through Sprocs, there is still an issue of constructing dynamic stuff, and "SQL execution strings" at times; right now we just format them "yyyymmdd" but I'm keen to know if there is a better solution)
Kristen |
|
|
|
Topic |
|
setting the date
