May 4, 2009 at 12:56 am
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
?
May 4, 2009 at 1:25 am
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
May 4, 2009 at 1:31 am
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
May 4, 2009 at 1:48 am
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/
May 4, 2009 at 1:58 am
Hi Adi,
Fine for the convert into varchar, but
OP shows convert to timestamp
ARUN SAS
May 4, 2009 at 2:15 am
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/
May 4, 2009 at 10:54 pm
DOne.....thanks so much for that query/....appreciate it.
May 5, 2009 at 7:44 am
Other method
declare @string char(12)
set @string = '200905031043'
select CONVERT (smalldatetime,stuff(stuff(@string,9,0,' '),12,0,':'))
Failing to plan is Planning to fail
May 5, 2009 at 7:54 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply