Help with varchar to date(urgent)

Question

Help with varchar to date(urgent)

lijusmail

Default port

Points: 1486
More actions
May 4, 2009 at 12:56 am

#131064

i have a column which holds the date in string format and is as given below
yyyymmddhhmm
is there a way i could convert the entire column into a date formatwith the timestamp in
?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

You need to convert the records from the date time to timestamp or change the table column schema from date time to timestamp?

ARUN SAS

lijusmail Default port Points: 1486 More actions · Answer 2

arun i would really appreciate you put a small

query for that one and let me know please

i dont want to change the schema

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Here is one way of doing so:

declare @string char(12)

set @string = '200905031043'

select CONVERT (smalldatetime,left(@string,8) + ' ' + substring(@string,9,2) + ':' + right(@string,2))

By the way why are you storing datetime information as varchar? Can you modify the table so it will store it as datetime or smalldatetime?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi Adi,

Fine for the convert into varchar, but

OP shows convert to timestamp

ARUN SAS

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

arun.sas (5/4/2009)
Hi Adi,
Fine for the convert into varchar, but
OP shows convert to timestamp
ARUN SAS

Timestamp has nothing to do with date. Timestamp is just a binary column that modifies its value automatically (in fact the user can not control the value of this column) each time that the user modifies the record. Since the original poster wrote that he has date and time as a string and he asked for “date formatwith the timestamp”, I assumed that he wanted to convert his string to date time. I admit that I didn’t notice that he is writing about timestamp, but even now I’m not sure that he actually meant the data type timestamp. In any case we’ll have to wait to the original poster’s input if he wants to use date time or timestamp.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lijusmail Default port Points: 1486 More actions · Answer 6

DOne.....thanks so much for that query/....appreciate it.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

Other method

declare @string char(12)

set @string = '200905031043'

select CONVERT (smalldatetime,stuff(stuff(@string,9,0,' '),12,0,':'))

Madhivanan

Failing to plan is Planning to fail

Johan Bijnens SSC Guru Points: 135357 More actions · Answer 8

Adi Cohn (5/4/2009)
arun.sas (5/4/2009)
Hi Adi,
Fine for the convert into varchar, but
OP shows convert to timestamp
ARUN SAS
Timestamp has nothing to do with date. Timestamp is just a binary column that modifies its value automatically (in fact the user can not control the value of this column) each time that the user modifies the record. Since the original poster wrote that he has date and time as a string and he asked for “date formatwith the timestamp”, I assumed that he wanted to convert his string to date time. I admit that I didn’t notice that he is writing about timestamp, but even now I’m not sure that he actually meant the data type timestamp. In any case we’ll have to wait to the original poster’s input if he wants to use date time or timestamp.
Adi

Considered the data provided by OP, we can assume the need for datetime conversion.

Although SQLserver has a proprietary data type "timestamp", other db engines use the (ISO/ANSI) Timestamp for datetime columns. (e.g. db2)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me