Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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.
0
Richard Quadling
Asked:
Richard Quadling
  • 7
  • 6
1 Solution
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
ptjcbCommented:
I agree with acperkins - use IS08601.
0
 
Richard QuadlingSenior Software DeverloperAuthor 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 DeverloperAuthor Commented:
Is the 'T' just a letter T or does it have another meaning? Can it be something else?
0
 
Richard QuadlingSenior Software DeverloperAuthor 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 DeverloperAuthor Commented:
0
 
Richard QuadlingSenior Software DeverloperAuthor 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 DeverloperAuthor 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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now