SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 setting the date
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

asm
Posting Yak Master

136 Posts

Posted - 11/15/2005 :  01:19:02  Show Profile  Reply with Quote

table datatype is smalldatetime

asm
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  01:54:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
How do you pass values to date column?
Give example

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

asm
Posting Yak Master

136 Posts

Posted - 11/15/2005 :  03:28:59  Show Profile  Reply with Quote

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  03:32:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

asm
Posting Yak Master

136 Posts

Posted - 11/15/2005 :  03:56:51  Show Profile  Reply with Quote
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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  04:14:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

asm
Posting Yak Master

136 Posts

Posted - 11/15/2005 :  04:31:31  Show Profile  Reply with Quote
BUT THE USER WANT THE DD/MM/YYYY FORMAT.

ASM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 11/15/2005 :  04:48:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

asm
Posting Yak Master

136 Posts

Posted - 11/15/2005 :  06:07:33  Show Profile  Reply with Quote
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"

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/15/2005 :  06:19:32  Show Profile  Reply with Quote
"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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  06:32:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/15/2005 :  06:42:07  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/15/2005 :  08:44:13  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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.

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  08:54:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Good Points. I need to think of that

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/15/2005 :  09:17:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/15/2005 :  09:29:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/15/2005 :  10:17:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/15/2005 :  11:05:50  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/15/2005 :  11:17:08  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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.

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/15/2005 :  11:59:36  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000