Date & time in SQLSERVER

[Metalhunter]

Hey

I have a problem to store a date and time in my server
gebruiker_creatiedatum and gebruiker_vervaldatum are in my datebase from the type Date/Time

this is my insertcode:

sqlInsPasw = "insert into logidPasw (gebruiker_creatiedatum, gebruiker_vervaldatum, gebruiker_stamnummer,gebruiker_logID, gebruiker_paswoord, gebruiker_vervaltnooit) values (CONVERT(datetime, '" & creatiedat & "'), CONVERT(datetime, '" & vervaldatum & "')," & "'" & strStamnummer & "'," & "'" & strGebruikerID & "'," & "'" & strPaswoord & "'," & "'" & vervaltnooit & "')"

How can I solve this.

And how can I insert a date and a time seppareted in the server.

Thanks

[MAK]

SQL Server has Datetime datatype and smalldatetime datatype. Both stores date and time together.

When situation like storing date and time separately, we usually store date in datetime datatype and time in varchar or another datetime.

your code looks like you are using VB.



create table x123 (date datetime, time datetime)
insert into x123 select convert (datetime,'5/25/2003'),convert (datetime,'7:54pm')

--SQL stores the values as
'2003-05-25 00:00:00.000' and '1900-01-01 19:54:00.000'

[Metalhunter]

Now i have this error: The conversion of char to datetime resulted in a datetime value out of range.

This is my code

datum = Date()
begindatum = Cdate(Day(datum)& "/" & Month(datum)& "/" & Year(datum))

sqlInsComdef = "insert into comdef(defect_nummer, defect_begindatum) values (CONVERT(int, '"& defectNummer & "'), CONVERT(datetime, '" & begindatum & "') )"
set rsComDef = conMed.Execute (sqlInsComdef)

Thanks

[MAK]

Worked fine for me.
--------------------
Private Sub Command1_Click()

datum = Date
begindatum = CDate(Day(datum) & "/" & Month(datum) & "/" & Year(datum))
sqlInsComdef = "insert into comdef(defect_nummer, defect_begindatum) values (CONVERT(int, '" & defectNummer & "'), CONVERT(datetime, '" & begindatum & "') )"
Text1.Text = sqlInsComdef
End Sub


Your 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.

This happens only if your date value is wrong. for example.


insert into comdef(defect_nummer, defect_begindatum) values (CONVERT(int, ''), CONVERT(datetime, '5/35/2003') )

5/35/2003 is a invalid date.

[andi_g69]

Yeah, but as you are using
datum = DATE()
the date value should never be invalid.

I think the problem originates from the date style:

datum = Date
begindatum = CDate(Day(datum) & "/" & Month(datum) & "/" & Year(datum))

will come out as "26/5/2003" on a dutch system, however SQL might expect the format MM/DD/YYYY (american notation)

You can force SQL to interpret the input string as DD/MM/YYYY by adding the style to the convert function:


sqlInsComdef = "insert into comdef(defect_nummer, defect_begindatum) values (CONVERT(int, '"& defectNummer & "'), CONVERT(datetime, '" & begindatum & "', 103) )"