?
Solved

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

Posted on 2005-03-15
14
Medium Priority
?
477 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:Richard Quadling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13544865
>>What format do I use to get a date and/or datetime into an MS SQL 2000 Server?
ISO8601 standard: yyyymmdd
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13544885
Sorry that is not correct for ISO8601, it should be:
yyyy-mm-ddThh:mi:ss.mmm(24h)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13544917
Here is an example using ISO8601:
Select CONVERT(varchar(30), GETDATE(), 126)

Output:
2005-03-15T10:52:26.260
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 27

Expert Comment

by:ptjcb
ID: 13544954
I agree with acperkins - use IS08601.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13544969
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13545059
Is the 'T' just a letter T or does it have another meaning? Can it be something else?
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13545075
Aha!

Thanks for that.

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

Richard.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13545084
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13545098
>>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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13672022
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13672025
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13672366
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 13678866
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13681398
>>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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question