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

Convert Datetime.

Hello All,

In following statement in SQL, I'm first converting 'IssueDate' with style 101 into 'nvarchar' then converting to Datatime.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')

Which is right in below one.

1. Do I need to first convert into nvarchar then datetime.
e.g. convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')

2. Otherwise can I directly convert into datetime.

convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/29/2004')

Please reply to me asap.

Regards,
M. G.Do this

WHERE DATEDIFF(d,'3/29/2004',IssuedDateX) < 0|||I would use:Convert(CHAR(10), Cert_WarehouseDetails.IssuedDateX, 121)if you intend to convert it back to a datetime (to strip off the time).

-PatP|||Doooh...

Either way it's a scan...

USE Northwind
GO

--Type [CTRL]+K

--Index Scan

SELECT OrderDate FROM Orders
WHERE DATEDIFF(d,'7/11/1996',OrderDate) < 0

--Index Seek

SELECT OrderDate FROM Orders
WHERE OrderDate <= '7/11/1996'
GO

Convert Datetime of query otimize.

Hello All,

In following statement in SQL, Which one should I use and why. My intention to get the record between the date and with style 101.
Which is in following is right one. If I use first('A') then it's little bit slower than second 'B'. So Please suggest me asap.

A.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')
and
(Cert_WarehouseDetails.IssuedDateX is NOT NULL AND
convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/11/2004')
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/12/2004')

B
Instead that Can I use like below, as

(convert(datetime,Cert_WarehouseDetails.IssuedDate X,101) >= '1/1/2004') AND
(Cert_WarehouseDetails.IssuedDateX is NOT NULL AND
convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/11/2004')

Please reply to me asap.

Regards,
M. J.

__________________Replace all occurrances of <= and >= along with the date specified with < original_date_plus_1 and > original_date_minus_1. This way you won't have to deal with CONVERT.|||Originally posted by rdjabarov
Replace all occurrances of <= and >= along with the date specified with < original_date_plus_1 and > original_date_minus_1. This way you won't have to deal with CONVERT.

Do I have to use 'nvarchar' while converting to datetime.