convert style for datetime

Hi,

If the datetime value is '20.01.2006', what will be the style in CONVERT function ?

Eg : select convert(datetime, '20.01.2006') gives datetime out-of-range error.

There is no style provided for dd.mm.yyyy in SQL Server documentation. Is there any way to do such conversion with this constant value of format dd.mm.yyyy ?

Please advice,

Thanks,
MiraJselect convert(varchar, getdate(),104)|||Hi i have created a table in SQL server with a field 'datetime'. I would like to convert into date. I understand that the above sysntax does the trick...but it wher do u put the column name you want to convert...Could you please give me the full syntax!!
Thanks:rolleyes:|||Hi i have created a table in SQL server with a field 'datetime'. I would like to convert into date. I understand that the above sysntax does the trick...but it wher do u put the column name you want to convert...Could you please give me the full syntax!!
Thanks:rolleyes:
Here's the full syntax...
convert(varchar(12),[field name],101)
But plz read CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) on BOL
Joydeep|||MiraJasmin,
you can use
select convert(datetime, '20.01.2006', 104)

migarich,
To convert to datetime, the best way is to specify the datatime in YYYYMMDD.
for example
insert into yourtable(yourdatetimefield)
select '20060120'

You can also use YYYY-MM-DD format

To convert from datetime to format in string, the best way is actually do this in your front-end application. If you want to do this in T-SQL, then you can use
convert(varchar(30), yourdatetimefield, styleno)
Refer to Books Online on the styleno