May 27, 2005 at 9:42 am
year=2005
Month=5
day=27
time=9:30
I have these values with me and I wanted to convert this to a datetime field.
SELECT CAST(year AS varchar)+'-'+CAST(month AS varchar)+'-'+CAST(day AS varchar)+' '+CAST(time as varchar)+':'+'00'
FROM tab
I am trying to convert this into datetime by using the above quesry.
But I wanted to convert month 5 as as 05 and time 9:30 as 09:30.
And also If i get day as 2 I need to convert it to 02.
Any help would be greatly appreciated.
Thanks.
May 27, 2005 at 10:10 am
select CONVERT ( datetime,getdate(),102 ) might do what you want:
2005-05-27 11:55:35.467
Lowell
May 27, 2005 at 10:10 am
This may not achieve what you want, but I am guessing this is for display reasons.
SELECT CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 101) + ' ' + CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 108)
I wasn't born stupid - I had to study.
May 27, 2005 at 10:34 am
I tried that too Lowell, but my setting in Query Analyzer yielded: 5/27/2005 12:18:47 PM.
That is why I suggested a more, (shall we say) convoluted answer.
I wasn't born stupid - I had to study.
May 27, 2005 at 12:57 pm
This should give you a hint:
select Right('00' + cast(month(getdate()) as varchar),2)
----
05
May 27, 2005 at 12:59 pm
Farrell... there is a 2 steps solution... i think this will work fine... at least it worked with my configuration...
But I suggest that you take a look at BOL, under topic CAST & CONVERT... there is a table comparing the diferent kinds of convertions that you can perform using cast and covert functions.
declare @date datetime
SELECT
@Date = convert(datetime, (year+'-'+month+'-'+day+' '+time+':00'), 102)
FROM YourTable
SELECT convert(varchar(20),@date,110)+' '+convert(varchar(20),@date,108)
Nicolas Donadio
Sr. Software Developer
DPS Automation
ARGENTINA
May 27, 2005 at 1:08 pm
Thanks Nicolas. I understand those. I misread the initial question and thought this was a date and not seperate fields for each part of a date. That is why I suggested the concatenation using a specific date so the leading zero's would display. ooops...
I wasn't born stupid - I had to study.
May 30, 2005 at 2:02 am
Another approach might be:
SELECT
CAST(
CAST(
(@year * 10000 +
@month * 100 +
@day)
AS CHAR(8))+' '+ @time
AS DATETIME)
Note, that this doesn't check for a valid date before trying to CAST to DATETIME. If you also need to do this, see if this helps: http://www.sql-server-performance.com/fk_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy