CONVERT & GETDATE Functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CONVERT & GETDATE Functions

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-16 : 10:22:11

I'm having a little trouble with the getdate() function. This is my sql statement . . .

sSQL = "Insert into TblRegistration (Name, PassWord, Email, Age, Gender, Promotions, TempReg, Publication, DateCreated) "
sSQL = sSQL & " values ('" & FullName & "', '" & PWD1 & "', '" & Email & "','" & Age & "','" & Gender & "','" & Promotions & "', 'YES', 'FW', getdate()) "

I get this error message when I try to run it.

Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type datetime to data type binary, table 'farmworldonlinecom.user1136558.TblRegistration', column 'DateCreated'. Use the CONVERT function to run this query.

I'm not sure how to implement the CONVERT function. My date should be formatted as mm/dd/yyyy

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 10:27:04

Use universal format yyyy-mm-dd or yyyymmdd

Convert(varchar,getdate(),112)

Madhivanan

Failing to plan is Planning to fail

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-16 : 10:45:24

This is the SQL I am now using . . .

sSQL = "Insert into TblRegistration (Name, PassWord, Email, Age, Gender, Promotions, TempReg, Publication, DateCreated) "
sSQL = sSQL & " values ('" & FullName & "', '" & PWD1 & "', '" & Email & "','" & Age & "','" & Gender & "','" & Promotions & "', 'YES', 'FW', Convert(varchar,getdate(),112)) "

In the database, there is nothing in the DateCreated field, even though the form does process now and all other fields work. In the database, DateCreated is a varchar.

Do I need to change to timestamp or something?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 11:02:43

>>In the database, DateCreated is a varchar.

Why did you use Varchar to store Date?
Use proper data type

Madhivanan

Failing to plan is Planning to fail

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-16 : 11:15:58

<<Why did you use Varchar to store Date?
<<Use proper data type

I was a little confused when you Convert statement used it . . .

Convert(varchar,getdate(),112))

I have it set as a date time and it seems to work. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 11:18:00

Why aren't you using stored procedures?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-16 : 11:20:28

Now I am getting an error when I try to pull the date that was inserted.

The code is . . .

While (NOT RsLogin.EOF)
RsLogin_numRows = RsLogin_numRows + 1
Dim SubscriberID : SubscriberID = RsLogin("SubscriberID")
if RsLogin("TempReg") = "YES" then
TempDaysRem = Round(RsLogin("DateCreated") + 14 - Now(),0)
If TempDaysRem > 0 AND RsLogin("Status") = "OK" then
TempLoginOK = "TRUE"
else
TempLoginOK = "FALSE"

I'm looking to see if the date that was inserted is less than 14 days ago. This is the error I am receivng.

Item cannot be found in the collection corresponding to the requested name or ordinal.

I'm not sure if this is a problem with the date or something else.

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-16 : 11:43:13

Nevermind, I fixed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources