Wanted: Cast iron way to insert dates and datetimes into SQL Server 2000.

Hi.

Should be easy this.

What format do I use to get a date and/or datetime into an MS SQL 2000 Server?

The server may or may not be set to use the same language as me. The server may or may not even have the same character set as me!

There must be an absolute format. One which all parts must be supplied and no chance of misunderstanding.

Ideally, something like ...

20050315144101GMT

From this it is 15th March 2005 at 2:41:01 PM GMT

I'm not sure how to get Daylight Saving Time in. Different countries may have there server setup incorrectly or differently to me. I know the date time and timezone and the DST setting, so I want to pass all of this in with the data.

Thanks,

Richard.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
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.

Anthony PerkinsCommented:
>>What format do I use to get a date and/or datetime into an MS SQL 2000 Server?
ISO8601 standard: yyyymmdd
0
Anthony PerkinsCommented:
Sorry that is not correct for ISO8601, it should be:
yyyy-mm-ddThh:mi:ss.mmm(24h)
0
Anthony PerkinsCommented:
Here is an example using ISO8601:
Select CONVERT(varchar(30), GETDATE(), 126)

Output:
2005-03-15T10:52:26.260
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ptjcbCommented:
I agree with acperkins - use IS08601.
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
Oh. Sorry missed a bit.

The times I am accumulating are from all over the world. For example.

2005-03-15 14:48:01.00 GMT (Me in the UK)
2005-03-15 09:48:01.00 EST (My New York counterpart's current time)
2005-03-15 15:48:01.00 CET (My boss in France)
2005-03-16 01:48:01.00 UTC/GMT+1000DST
All of these occur at the same time. I need to be able to store this in the DB as is.

When I extract from the DB, can I translate them to a required time?

By this I mean that when a user logs in, from their details, I know the timezone they are in. They would like the option to show the recorded times in either the original time, or as local time.

Richard.
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
Is the 'T' just a letter T or does it have another meaning? Can it be something else?
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
Aha!

Thanks for that.

http://www.w3.org/TR/NOTE-datetime

Richard.
0
Anthony PerkinsCommented:
Then we are talking about two different issues.  What date you store is really a matter of design,  But if "countries may have there server setup incorrectly or differently to me" you are going to be hard pressed to get a good time.  

In general, this is best handled from the front-end, while MS SQL Server can give you GETDATE() (local time) and GETUTCDATE() (UTC) that is the extent of it.  In order to calculate for DST and timezones you should rely on the Windows or Java API to get them.
0
Anthony PerkinsCommented:
>>Is the 'T' just a letter T or does it have another meaning? Can it be something else?<<
You got it.  T stands for Time element.
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
This query ...

SELECT
      SUCODE,
      SUNAME,
      POH_ORDER_NUMBR,
      POH_ACCOUNT,
      POH_CONTRACT,
      POH_AUTH_SEQ,
      POH_USER_AUTH,
      POH_DATE_AUTH,
      POH_USER1,
      POH_USER2,
      POH_BATCH_REF,
      POD_DETAIL,
      POD_PRICE_CODE,
      VEH_REASON,
      VEH_MILEAGE
FROM
      POP_HEADER
            LEFT OUTER JOIN PL_ACCOUNTS ON POH_ACCOUNT = SUCODE
            LEFT OUTER JOIN POP_DETAIL ON POH_ORDER_NUMBR = POD_ORDER_NO
                  LEFT OUTER JOIN BANV_VEHICLE ON POD_PRIMARY = VEH_PO_DETAIL_NUM
WHERE
      (
            (POH_CONTRACT = 'ALLTRUCK') OR
            (POH_CONTRACT = 'ARGOS') OR
            (POH_CONTRACT = 'ARGOS DIRECT') OR
            (POH_CONTRACT = 'BAGNALL & MORRIS') OR
            (POH_CONTRACT = 'BRAKE BROS') OR
            (POH_CONTRACT = 'CHICHESTER COUNCIL') OR
            (POH_CONTRACT = 'CORY CARRICK') OR
            (POH_CONTRACT = 'CORY LINCOLN') OR
            (POH_CONTRACT = 'CORY RUSHDEN') OR (POH_CONTRACT = 'CORY RUTLAND') OR
            (POH_CONTRACT = 'CORY SOUTHEND') OR
            (POH_CONTRACT = 'ENDEVA') OR
            (POH_CONTRACT = 'EUROWAY') OR
            (POH_CONTRACT = 'EVANS TRANSPORT') OR
            (POH_CONTRACT = 'EXPRESS DAIRIES') OR
            (POH_CONTRACT = 'F&P WHOLESALE') OR
            (POH_CONTRACT = 'FREIGHTLINER') OR
            (POH_CONTRACT = 'HARTSHORNE A/STONE') OR
            (POH_CONTRACT = 'HARTSHORNE') OR
            (POH_CONTRACT = 'HILL HIRE') OR
            (POH_CONTRACT = 'HORSHAM COUNCIL') OR
            (POH_CONTRACT = 'INTERBREW') OR
            (POH_CONTRACT = 'JRT') OR
            (POH_CONTRACT = 'MANVIK HIRE') OR
            (POH_CONTRACT = 'MCT&B') OR
            (POH_CONTRACT = 'SALVESEN UK') OR
            (POH_CONTRACT = 'SFS COLCHESTER') OR
            (POH_CONTRACT = 'SFS DAVENTRY') OR
            (POH_CONTRACT = 'SFS KETTERING') OR
            (POH_CONTRACT = 'SFS NEWCASTLE') OR
            (POH_CONTRACT = 'SFS NORTH WARWICK') OR
            (POH_CONTRACT = 'SFS NORTHAMPTON NBC') OR
            (POH_CONTRACT = 'SFS NORTHAMPTON') OR
            (POH_CONTRACT = 'SFS PETERBOROUGH') OR
            (POH_CONTRACT = 'SFS SOUTH NORTHANTS') OR
            (POH_CONTRACT = 'SFS STOCKTON') OR
            (POH_CONTRACT = 'T&S') OR
            (POH_CONTRACT = 'TESCO') OR
            (POH_CONTRACT = 'UPS') OR
            (POH_CONTRACT = 'VIRIDOR R & L') OR
            (POH_CONTRACT = 'VIRIDOR') OR
            (POH_CONTRACT = 'WISEMAN DAIRIES')
      ) AND
      (POH_DATE_AUTH>='2005-03-31T00:00:00') AND
      (POH_DATE_AUTH<='2005-03-31T23:59:59') AND
      (POD_PRICE_CODE <> 'TRAILER INSPECT') AND /* Do not show trailer inspections */
      (POD_PRICE_CODE <> 'TRUCK INSPECT') AND /* Do not show truck inspections */
      (POD_PRICE_CODE <> 'ERRORS') AND /* Do not show errors */
      (POD_PRICE_CODE <> 'UNDER/OVER CHGS') /* Do not show Under/Over charges */
ORDER BY
      SUCODE,
      POH_ORDER_NUMBR

generates ...

/*-----------------------------
SELECT
      SUCODE,
      SUNAME,
-----------------------------*/
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.


in Query Analyzer.
0
Anthony PerkinsCommented:
I have never seen ISO8601 represented as anything but yyyy-mm-ddThh:mm:ss.mmm.  So if it is failing for you I stand corrected.  In any case, it looks like Scott has you covered.
0
Richard QuadlingSenior Software DeveloperAuthor Commented:
What is daft is that it was working. We moved the Databases from an old server to a nice new 2K server. I can't work out what has changed, but it does seem that the CCYYMMDD HH:MM:SS.SSS works. Still not perfect as there is no timezone/DST facility in this format. It is a shame there is no absolute timeformat.

0
Anthony PerkinsCommented:
>>Still not perfect as there is no timezone/DST facility in this format.<<
Actually, it looks like MS SQL Server implementation of ISO8601 does not support timezones.  So it may not be such a loss.

>>It is a shame there is no absolute timeformat.<<
I agree.  Hopefully this will all be resolved with SQL 2005 ... <g>
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.