Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 15th, 2006, 12:45 PM
Registered User
Join Date: Apr 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default date time addition

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

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


Old April 16th, 2006, 03:50 AM
Authorized User
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts

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!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting Time from Date/Time Navy1991_1 XSLT 2 June 3rd, 2008 12:03 PM
date and time in EST time zone anboss XSLT 1 May 21st, 2008 01:42 PM
How to retieve only the date not date and time debjanib ASP.NET 1.0 and 1.1 Professional 2 July 25th, 2006 01:10 PM
insert system date. no date and time jimmy Access VBA 6 November 20th, 2003 01:11 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.