Insert Current Date into SQL

[Pamy]

Hi,
I would like to insert current date (in dd/mm/yyyy)format in the SQL Server.
Please teach me how to write the code.

sql = "INSERT INTO tbattendance([User],Email,TopicID,DateSubmitted,Dept) values('?','?','?','?','?')"
sql = Replace(sql, "?", Name, 1, 1)
sql = Replace(sql, "?", Email, 1, 1)
sql = Replace(sql, "?", TopicID, 1, 1)
sql = Replace(sql, "?", Current_Timestamp , 1, 1)
sql = Replace(sql, "?", Dept, 1, 1)

The Current_TimeStamp return a different format.

 

[swampBoogie]

You are confusing the display format and the storage format. Dates are not stored as dd/mm/yyyy but rather as numerical values. You should use the current_timestamp function to store datetime values. To retrieve a date in a certain format use the convert function in your select statement.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

 

[swampBoogie]

Direct link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

 

[Kurniadi]

You can use the logical like this...

DateSubmitted = CONVERT(varchar(10),Current_Timestamp, 103)

or you can use CAST. Please open "SQL Server Books Online" with the topic "CONVERT"


Kurniadi, Indonesia

 

[tlbroadbent]

Take a look at the following article.

Handling Dates in SQL Server

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Pamy]

Hi all,

I tried to change the coding to

sql = "INSERT INTO tbattendance([User],Email,TopicID,DateSubmitted,Dept) values('?','?','?','?','?')"
sql = Replace(sql, "?", Name, 1, 1)
sql = Replace(sql, "?", Email, 1, 1)
sql = Replace(sql, "?", TopicID, 1, 1)
sql = Replace(sql, "?", convert(datetime,Current_Timestamp, 103),1,1)
sql = Replace(sql, "?", Dept, 1, 1)

but still doesn't work

 

[Kurniadi]

Hi...
Shouldn't use DATETIME, but please use VARCHAR(10), in SQL Server is no problem.

And the important, please look at Regional Setting, change the SHORT DATE STYLE to dd/mm/yyyy or mm/dd/yyyy and please use 101 or 103 for anticipate SHORT DATE STYLE. For the current date, please use GETDATE(). I try it and everything is oke. Please try it with 101 or 103...

DateSubmitted = CONVERT(varchar(10),Current_Timestamp, 101)
DateSubmitted = CONVERT(varchar(10),Current_Timestamp, 103)
DateSubmitted = CONVERT(varchar(10),GETDATE(), 101)
DateSubmitted = CONVERT(varchar(10),GETDATE(), 103)

Kurniadi, Indonesia

 

[Kurniadi]

Hi Pamy...

Sorry, please use 101 or 102.

 

[Pamy]

Hi Kurniadi,

Tks for yr prompt reply. But I still having problem after trying out yr example.

================================================
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'varchar'
===================================================
The datatype that stored the current date in the SQL Table is datetime. Not sure if this is causing the problem

 

[swampBoogie]

You shouldn't use convert when storing data. That's only relevant when retrieving data. Just use current_timestamp without any frills.

 

[Pamy]

I have tried to current_timestamp. But the date capture is incorrect. For ex..if the current date is 27/2/3, it stored in the database as 1/1/1900...

 

[swampBoogie]

That's because with you code your are attempting to store the value 'current_timestamp'.

sql = "INSERT INTO tbattendance([User],Email,TopicID,DateSubmitted,Dept) values('?','?','?',current_timestamp,'?')"
sql = Replace(sql, "?", Name, 1, 1)
sql = Replace(sql, "?", Email, 1, 1)
sql = Replace(sql, "?", TopicID, 1, 1)
sql = Replace(sql, "?", Dept, 1, 1)

 

[Pamy]

Hi SwampBoogie,

My coding works!!!!
Thanks for your great advise....