insert data type smalldatetime

[jamesd0142]

i have a column in my database called passwordchanged of datatype smalldatetime(4)

i want to add a record to this with the format 'DD/MM/YYYY' how can i do this?

[amitpatel66]

i have a column in my database called passwordchanged of datatype smalldatetime(4)

i want to add a record to this with the format 'DD/MM/YYYY' how can i do this?

You need to alter your table and increase the size of the column so that is can hold the date value in the format 'dd/mm/yyyy'

[jamesd0142]

You need to alter your table and increase the size of the column so that is can hold the date value in the format 'dd/mm/yyyy'

there is already data in the field in that format (dd/mm/yyyy) which is put there by another programme.

This is why im so confused,

[amitpatel66]

there is already data in the field in that format (dd/mm/yyyy) which is put there by another programme.

This is why im so confused,

James,

In that case why dont you try inserting the data. POST back in case if it generates any error.

[jamesd0142]

i managed to find the answer,

(select convert(varchar(20), convert(datetime, getdate()), 101)
 

Full code is

declare @a varchar(20)
Set @a = (select convert(varchar(20), convert(datetime, getdate()), 101))
update Users
SET [Password] = DD3_Password, PasswordChanged = @a
from temptable2
where Users.Login = temptable2.Login
 

cheers