Date Time Conversion

Notes Using SQL Server 2000

I need date time in the below format using current date/time before the slash and and current date/time + 6 days after the slash added with the time zone offset as indicated below.

2011-03-01T13:00-0800/2011-03-11T15:30-0800


Further description of format:

Start and end dates separated by a forward slash (/). The start is specified by the format (YYYY-MM-DD), followed by the letter ‘T’, the time of the day when the sale starts, followed by an expression of the timezone for the sale. The end date should be in the same format.

Thanks in advance
ubsmailAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
1. remove from sql 2000 and add to MS SQL Server if it is sql server 2000

2. what timezone is supposed to be represented? how will it be determined?


like this if you are relying on the underlying sql server instance information
select convert(char(16),now,126)+'-'
     +right('00'+convert(varchar(2),nowtz/60),2)+right('00'+convert(varchar(2),nowtz % 60),2)
     +'/'
     +convert(char(16),now6,126)+'-'
     +right('00'+convert(varchar(2),nowtz/60),2)+right('00'+convert(varchar(2),nowtz % 60),2)

  from (select datediff(minute,now,nowutc) as nowtz,now,dateadd(dd,6,now) as now6
          from (select getdate() as now,getutcdate as nowutc) as x
       ) as x

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
IIRC, SQL 2000 has no "understanding" of time zone offset, so that must be in some column in your table (?).

REPLACE(CONVERT(varchar(16), GETDATE(), 121), SPACE(1), 'T') + <time_zone_offset_value> + '/' +
REPLACE(CONVERT(varchar(16), DATEADD(DAY, 6, GETDATE()), 121), SPACE(1), 'T') + <time_zone_offset_value>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.