Concatenating date and time

[cwolgamott]

Hello. I am building some asp pages. On one page I allow the user to select a date in this format (6/26/2002). I, then, pass this value on to another pages where I want to insert it into a SQL table field that is defined as datetime. However, before I insert this field, I need to add the following string ("12:00:00 AM&quot to the end of the date with a space in between the date and time and then pass that whole string into the SQL table field. I have tried to put the date into a variable and the time into a variable, add them together and then pass the combination of the two into the table but it does not work. I would greatly appreciate any suggestions. Thank you.

 

[Gorkem]

Example code:

<%
datevar = &quot;01/01/2002&quot;
timevar = &quot;12:00:00 AM&quot;

datevar = datevar & &quot; &quot; & timevar

'now insert the datevar into your database
'the following code assumes you have already
'opened a DB connection and using ADODB.COMMAND

cmd.commandstring = &quot;INSERT INTO

(DATECOLUMN) VALUES ('&quot; & datevar & &quot;')&quot;
cmd.Execute

'rest of your code here...

%>

 

[cwolgamott]

Thank you so much for your reply. I really appreciate it. However, I tried the code that you gave me and it still does not insert the 12:00:00 AM into my database field. It only inserts the date. Here is the code that I entered in my asp page:

dim dateVar
dim timeVar
dateVar = Request(&quot;date1&quot
timeVar = &quot;12:00:00 AM&quot;
dateVar = dateVar & &quot; &quot; & timeVar

SQL = &quot; INSERT INTO CaseInfo (CaseInfo.casenum, CaseInfo.caller, CaseInfo.subject, CaseInfo.description, CaseInfo.openedby, CaseInfo.dateopened, CaseInfo.daterequested, CaseInfo.dateneeded, CaseInfo.dateestimated, CaseInfo.iscompleted, CaseInfo.respondedto, CaseInfo.percentcomplete, CaseInfo.status) &quot;
SQL = SQL + &quot;VALUES (&quot; + CStr(newCaseNumber) + &quot;, &quot; + &quot;'&quot; + inputFullName + &quot;', &quot; + &quot;'&quot; + Request(&quot;subject&quot + &quot;', &quot; + &quot;'&quot; + Request(&quot;description&quot + &quot;', &quot; + &quot;'&quot; + &quot;INTRANET&quot; + &quot;', &quot; + &quot;GETDATE()&quot; + &quot;, &quot; + &quot;'&quot; + dateVar + &quot;', &quot; + &quot;'&quot; + dateVar + &quot;', &quot; + &quot;GETDATE()&quot; + &quot;, &quot; + &quot;'&quot; + &quot;N&quot; + &quot;', &quot; + &quot;'&quot; + &quot;N&quot; + &quot;', &quot; + &quot;0&quot; + &quot;, &quot; + &quot;'&quot; + &quot;NOT STARTED&quot; + &quot;')&quot;
conn1.Execute(SQL)

I would greatly appreciate any suggestions as to what I might be doing wrong. Thank you so much again for your response.

 

[onpnt]

just incase your looking to insert the current time
timevar = Time
datevar = datevar & &quot; &quot; & timevar
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[onpnt]

test the datevar to make certain it has the correct value in it

alert datevar
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[Gorkem]

I cleaned up your SQL statement a little.. try this and see if it works..

SQL = SQL & &quot;VALUES (&quot; & CStr(newCaseNumber) & &quot;, '&quot; & inputFullName & &quot;','&quot; & Request(&quot;subject&quot & &quot;','&quot; & Request(&quot;description&quot & &quot;', 'INTRANET', GETDATE(),'&quot; & dateVar & &quot;','&quot; & dateVar & &quot;', GETDATE(), 'N', 'N', 0, 'NOT STARTED')&quot;  

Cheers,

Gorkem.

 

[cwolgamott]

Thank you so much for all of your replies and suggestions. It actually seems to work the best when I enter the current time with the date. I greatly appreciate all of the suggestions and replies. Thank you so much.

 

[onpnt]

there may be a format problem in the DB. Try and change the format if it is set to only take date mm/dd/yyyy
The code looks fine to me.
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[Gorkem]

You may also want to check to make sure that the DateVar is in fact a true date..

You may want to use the isDate function.

Ex..

if isDate(DateVar) = true then
'it is a date
else
'this is not a true date
end if

Cheers,

Gorkem.

 

[Dynapen]

You do understand that you don't actually have to have a time on the date to insert it into the DB right?

If you take a date, formatted as you have above, and &quot;as a literal&quot; pass it into the datefield on the DB without a time being added to it, the DB will automatically set the time to 12:00 AM.

Insert into table (datefield) value
('date')

This will work. If when you look at the database and all you see is the date and not the time, then that is becuase of how you have the date field set to display in the DB side. (Which it sounds like is in Access) The money's gone, the brain is shot.....but the liquor we still got.

 

[cwolgamott]

Thank you for the reply. I greatly appreciate it. I did not know that you don't have to put the time in with the date. I did not see the time in the database, but it would make sense if the database was set up that way, which I am unsure if that is how it is set up or not. Actually, I am using a SQL Server 2000 database. Thanks again for your response.