hello friends,
              i need help in datetime in MS SQL SERVER 2000.

my initial query is:
select year(datetime) as year, month(datetime) as month,
day(datetime) as day,convert(datetime,(cast(year(datetime) as nvarchar(4))+'-'+cast(month(datetime) as nvarchar(2))
       +'-'+cast(day(datetime) as nvarchar(2))+' '
    +cast(datepart(hour,datetime) as nvarchar(2))+':'+cast(datepart(minute,datetime) as nvarchar(2))+':'
     +cast(datepart(second,datetime) as nvarchar(2))),20) as converted datetime

from weatherdata
where [datetime2] is null

output:
year month day converteddatetime
2005 1 2 2005-01-02 05:56:00.000

i do not want the minutes and seconds in the converteddatetime and i am not able to run it.
if i take the minutes and seconds statmenets in the query
"(datepart(minute,datetime) as nvarchar(2))+':'+cast(datepart(second,datetime) as nvarchar(2))),"
it is not working.

so please friends could you help me out in this query?


i want the output to be as below:
year month day converteddatetime
2005 1 2 2005-01-02 05:00:00.000

thanks,
raj.

Hi Titan!

The following query is based on your query with some corrections -

----------------------------------------------------------------------


select year(datetime) as year,
    month(datetime) as month,
    day(datetime) as day ,
    cast(year(datetime) as nvarchar(4)) +
         '-' + cast(month(datetime) as nvarchar(2)) +
         '-'+ cast(day(datetime) as nvarchar(2)) + ' '
        + cast(datepart(hour,datetime) as nvarchar(2))+':00:00:000' as converteddatetime
from weatherdata
where [datetime2] is null

----------------------------------------------------------------------

You wrote that - "i do not want the minutes and seconds in the converteddatetime". So, I have removed those in modified query.

I hope this query must work well.
Reply ASAP!