converting to datetime data type
-
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.
-
select CONVERT ( datetime,getdate(),102 ) might do what you want:
2005-05-27 11:55:35.467
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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.
-
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.
-
This should give you a hint:
select Right('00' + cast(month(getdate()) as varchar),2)
----
05
-
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
-
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.
-
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