SQL Server and datetime field

Results 1 to 4 of 4

Thread: SQL Server and datetime field

  1. #1
    Perini14 Guest

    Default SQL Server and datetime field

    I can&#039;t insert a system date in SQL server table datetime field !!!<BR><BR>I&#039;ve tryed this:<BR><BR>sql="insert into table1 (mydate) values (" & date & ")"<BR>conn.execute(sql)<BR><BR>Mydate is a field of type datetime. I get an error message saying sql string is not ok. That&#039;s because my system date is formated like this "28.08.2001". <BR><BR>When I use this string, it works fine:<BR><BR>d=#28/08/01#<BR>sql="insert into table1 (mydate) values (" & d & ")"<BR><BR>So I&#039;ve tryed to format my date to look like that d variable:<BR><BR>sql="insert into table1 (mydate) values (" & format(date, "dd/mm/yy") & ")"<BR><BR>But in this case, SQL puts a strange date in my field, a date from year 1900!<BR>It looks something like 05/01/1900<BR><BR>What&#039;s the problem? How to do it ??<BR><BR>Please help !<BR><BR>Thanks<BR><BR>

  2. #2
    Heaven's Martini Guest

    Default RE: SQL Server and datetime field

    hmm<BR><BR><BR>sql="insert into table1 (mydate) values (" & d & ")"<BR><BR><BR>well, with sql to insert vbscript date vars into the statement <BR>sql="insert into table1 (mydate) values (&#039;"& d &"&#039;)"<BR>this will work if d is a valid date.<BR>you can use Cast in the sqlstatement or CDate in vbscript to convert the var to a date and you can use isDate to check if the string is a var date<BR><BR>the whole reason for the 1/1/1900 thing is if you try to insert a null var into sql server, it does that.<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: SQL Server and datetime field

    Try using this:<BR>Dim Today<BR>Dim Mydate<BR><BR>today = Date() <BR>Mydate = Year(today) & Right("0" & Month(today),2) & Right("0" & Day(today),2)<BR><BR>That will give you a date to insert which has no &#039;/-.&#039;

  4. #4
    Join Date
    Dec 1969

    Default True, but he still needs...

    ...the &#039;...&#039; around the string to make SQL Server accept it. As Mr. Martini suggested.<BR><BR>See the ASPFAQs.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts