DataType conversion help

[gtb12314]

Hi All,

I am using SQL SERVER 2005 and one of my table has varchar2 field whose value is date & time.

I want to know how to convert varchar2 field into just date datatype.

Any help is greatly appreciated.

Thanks in advance

 

[RiverGuy]

Need an example of how the values are stored in the column.

 

[gtb12314]

I am sorry my varchar2 field has only date in it. And it's stored as 2008-08-01. I want to convert this into date datatype.

please help me out.

 

[CaptainD]

Provided the data that is in the field is a valid date then you should be able to use

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

http://technet.microsoft.com/en-us/library/ms187928.aspx

 

[RiverGuy]

Try this

SELECT CONVERT(DATETIME, REPLACE(YourColumnName, '-', ''))

 

[RiverGuy]

By the way, the code above assumes your example is specifying 08 as the month and 01 as the date.

 

[gtb12314]

my field name is receiveddate and its datatype is varchar2(10) and its value is 2008-08-01. So can I do like this as below:

CONVERT ( date , receiveddate )

 

[CaptainD]

CONVERT(DATETIME, receivedate, 101)

 

[CaptainD]

Or, if you want yyyy-mm-dd

CONVERT(DATETIME, receivedate, 112)

 

[gtb12314]

I cannot convert just to date instead of datetime.
e.g. CONVERT ( date , receiveddate, 101 )

 

[CaptainD]

MS SQL only has DATETIME and SMALLDATETIME

You have to format it to show just the date.

Look at the link I posted, it should explain what you need.

 

[RiverGuy]

There is no data type in SQL Server 2005 called DATE. Are you sure you're not using Oracle or using a user defined data type in SQL Server?

 

[gtb12314]

I tried both the below solutions but still I am getting the time along with date.

CONVERT(DATETIME, receivedate, 112)
CONVERT(DATETIME, receivedate, 101

 

[RiverGuy]

Then you're not wanting to change the actual data type of the value. A DATETIME/SMALLDATETIME has a time component. There's no way to leave it off. You want a VARCHAR representation of it in a specified format. But it seems as though your original string value has the time left off already. So in that case, please provide a "before" and "after" of your example as far as how you want it formatted. Better yet, look up "CAST AND CONVERT" in books online (the SQL Server Help). It should give you some insight to the styles you can cast to.

 

[CaptainD]

Converting to a datetime will show the time.

Converting to NVARCHAR() will format as expected.

Here are two examples to run in Query analyzer

One converts a string value to a datetime the other converts the same data from a datetime to an NVARCHAR(10)

DECLARE @tTemp1 TABLE (iID INT IDENTITY(1,1) PRIMARY KEY,
 dDateTime DATETIME)

INSERT INTO @tTemp1(dDateTime)
VALUES('2008-01-05')
INSERT INTO @tTemp1(dDateTime)
VALUES('2008-01-06')
INSERT INTO @tTemp1(dDateTime)
VALUES('2008-01-07')


SELECT CONVERT(NVARCHAR(10), dDateTime, 101)AS 'TimeToChar' FROM @tTemp1

DECLARE @tTemp2 TABLE (iID INT IDENTITY(1,1) PRIMARY KEY,
 sDateTime NVARCHAR(10))

INSERT INTO @tTemp2(sDateTime)
VALUES('2008-01-05')
INSERT INTO @tTemp2(sDateTime)
VALUES('2008-01-06')
INSERT INTO @tTemp2(sDateTime)
VALUES('2008-01-07')


SELECT CONVERT(DATETIME, sDateTime, 101)AS [CharToTime]FROM @tTemp2

So, trying to convert to a datetime will not give you what you want, it works the other way around.

Are you tryingto change it to "date" for a reason, it sounds like it is already in that format?