Transact SQL Help

[CiPiD]

Possibly a really dumb question but I am still a SQL Padawan.

Running SQL 2000 on Windows 2003
if i issue the command select Getdate() i get the reply in the format yyyy-mm-dd hh:mm:ss.mmm which is perfect.

I have a table that i want to be able to update with a script and it has a datetime column in the format that GetDate is showing.

If I use getdate() as a value to enter into the table, however, it enters in the format of MMM dd yyyy hh:mmAM which is not interpreted byt the program running on the DB.

I have also tried putting it in as a variable with the same results.

i.e.

Declare @timefield datetime
Set @timefield = (select getdate())
print @timefied

How can I get the select/insert statements to use the exact format that getdate uses when I call it on its own? Why does it change it? Is there an easier way?

Any help at all would be great!

EDIT: Added OS and SQL versions

[azimmer]

Possibly a really dumb question but I am still a SQL Padawan.

Running SQL 2000 on Windows 2003
if i issue the command select Getdate() i get the reply in the format yyyy-mm-dd hh:mm:ss.mmm which is perfect.

I have a table that i want to be able to update with a script and it has a datetime column in the format that GetDate is showing.

If I use getdate() as a value to enter into the table, however, it enters in the format of MMM dd yyyy hh:mmAM which is not interpreted byt the program running on the DB.

I have also tried putting it in as a variable with the same results.

i.e.

Declare @timefield datetime
Set @timefield = (select getdate())
print @timefied

How can I get the select/insert statements to use the exact format that getdate uses when I call it on its own? Why does it change it? Is there an easier way?

Any help at all would be great!

EDIT: Added OS and SQL versions

Use the convert() function, like this:

select convert(datetim e,getdate(),121 )

[CiPiD]

OK, I tried the following....

Declare @timetest datetime
Set @timetest = (select convert(datetim e,getdate(),121 ))
Print @timetest

and it Prints in the format

mmm dd yyy h:mmPM again which is still no good.

Any other ideas?

[azimmer]

OK, I tried the following....

Declare @timetest datetime
Set @timetest = (select convert(datetim e,getdate(),121 ))
Print @timetest

and it Prints in the format

mmm dd yyy h:mmPM again which is still no good.

Any other ideas?

hold on for a moment, pls

[azimmer]

OK, I tried the following....

Declare @timetest datetime
Set @timetest = (select convert(datetim e,getdate(),121 ))
Print @timetest

and it Prints in the format

mmm dd yyy h:mmPM again which is still no good.

Any other ideas?

OK, a bit of background. Conversion takes place for the sake of the output only; MSSQL always stores the date in its internal way (you don't have to know/care about at this point). Your way of doing does the following: the second line first converts the date to your liking, but then it converts back to the internal format (implicit conversion). The right way of doing this is the following:

Declare @timetest varchar(50)
select @timetest = convert(varchar(50),getdate(),121)
Print @timetest

Remember: conversion is for output only, once you assign a converted value to a datetime type variable (or column) you lose the formatting. (If you work in a multicultural environment you'll find it makes a lot of sense.)

PS: My previous code wasn't quit good because I converted it to datetime and thereby lost the result of the conversion immediately...

[CiPiD]

Brilliant! Thanks. I will keep that one in mind!