[RESOLVED] Date??

[omarr226]

hi guyz i want to know how to store dates in database of sql server?
i m taking input from da user in separate combo boxes... 1st one is of months and den 2nd is of days and 3rd is of year....
upon submitting da desired values..how will dey be stored in database... ??
dey r collected as string values in da next page...den how will dey be stored??
i not only want to store dem for display purposes...but also dey should be used in searching and calculation (for example in calculating age)
also wat will be da datatype in sql server database??

[lmf232s]

Data Types options:
DateTime
SmallDateTime

Format or each:
DateTime = 10/26/2005 10:59:24 PM
SmallDateTime = 10/26/2005

It is fine that the values are a string as long as they are a valid date when
you attempt to write them to the database.

If IsDate(txtDate) Then
'This value is a proper date so lets add it
Else
'This value is not a valid date do not add it.
End If
sSQL = "INSERT INTO TABLE (MYDATE) " & _
"VALUES ('" & txtDate & "')"


IMO working w/ the SmallDataTime for searching is easier than using the DateTime format.

W/ SmallDateTime i can just do something like this

SELECT * FROM TABLE WHERE MyDate < Date()

But sometimes you will not get the results that you would expect w/ the DateTime datatype which will require you to
convert the DateTime to a smallDateTime like such.

SELECT * from TABLE WHERE Convert(varchar(10), CREATEDATE, 101) = '11/15/2005'

Where CREATEDate is my database field name.

But again it will depend on what your trying to capture. I have a time tracking application where i need the date and the time so that i can calculate the total hours worked.

If you dont need the time then use SmallDateTime as your datatype.

[russell]

lmf232s, I hate to disagree with you but...

Format:
DateTime: 2006-06-01 13:27:49.387
SmallDatetime: 2006-06-01 13:28:00

And from BOL

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Try this query:

SELECT Cast(CURRENT_TIMESTAMP as smalldatetime) smDate,
Cast(CURRENT_TIMESTAMP as datetime) lngDate,
Convert(smalldatetime, left(CURRENT_TIMESTAMP, 11), 101) dtToday
first field demonstrates smalldatetime, 2nd field demonstrates datetime, 3rd field shows how to get just the date portion, stripping off the time.

[russell]

btw -- i use smalldatetime wherever possible. rarely do i need to store dates that far in the past or future, nor worry about seconds or milliseconds...

[lmf232s]

Actually your wrong russell........

Just kidding :D

I just relized this about 2 hours ago as i ran accross my own problems w/ dates and the database. I ended up on MS website and read about the dates and learned that i posted the wrong information. I was coming back to correct my self but you beat me to it. :eek:

What mixed me up is that when i write the value of NOW() to the table i always have my field set to DateTime but if i used Date() i always use SmallDate (dont ask why this is. Its probabley something that i saw one day along time ago and have just followed that methodology to this day without really knowing why). So you can see when i look at the database it appears that they store the values differently which is what i based my response on which is way wrong. Which bums me out but on the other hand i did learn something new.

[omarr226]

wat aby my prb??
wat exactly shud i do now??

[russell]

i would combine the form values into one date then store them as smalldatetime in sql server.

for example

Dim dt
Dim mm, yy, dd

yy Request.Form("year")
mm = Request.Form("month")
dd= Request.Form("day")

dt = mm & "/" & dd& "/" & yy

Then insert dt into your smalldatetime field in the db

[omarr226]

i'll try it wen i'll get home...

[omarr226]

hi dere..i m back..!
actually i was out of town..newayz...
i tried it but da error is shown>> String or binary data wud be truncated.
now wat??

[russell]

welcome back. what data type did you make the date field? what value were you trying to insert. that error message is basically an overflow -- when you try to store a value larger than fits in the db field. for example, if you have a varchar(5) and try to cram 6 characters into it...

[omarr226]

oh i got it rite..!
just changed da datatype from smalldatetime to datetime....!
its working now..thanx!
btw i want to ask will i be able to calculate the dayz difference like if i do the following>>>

dim diff

diff= Date() - rs(edate)
??

[russell]

diff = Date() - CDate(rs(edate))

Or

diff = datediff("d", Date(), rs(edate))

[omarr226]

CDate() converts into date format?

and wat does "d" have to do in the second function?

just asking...;)

[russell]

W3Schools -- Datediff function (http://www.w3schools.com/vbscript/func_datediff.asp)

[omarr226]

oh thanx a lot..!(y)