asked on

URGENT! Getting dates in / out of MS SQL Server.

Hi.

On my SQL 2000 SP3a Developer Edition, I use ...

2005-03-31T15:58:30

I can use this as ...

where date='2005-03-31T15:58:30'

or

SET date='2005-03-31T15:58:30'

and all is OK.

On the production server, I have to use ...

2005-31-03T15:58:30

Which is CLEARLY not the same thing.

The question is ...

for ANY MS SQL server, in any timezone, in any country, what is the correct format for dates when I want to supply it to the server, either as part of a where clause or as part of an INSERT/UPDATE? I do NOT care about the format returned from a SELECT (as this will be locale dependant).

Thought I had it!

Richard.

Scott Pletcher

I think it's this:

yyyymmdd hh:mm:ss.sss

Scott Pletcher

For example:

SELECT CAST('20050331 18:04:57.487' AS DATETIME)

With *no* editing characters, SQL *always* assumes yyyymmdd.

Richard Quadling

ASKER

Should I be casting the datetime string I have to a DATETIME when I am using it as part of a WHERE clause or a SET clause also?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Quadling

ASKER

It seems the only guaranteed way is to cast or convert (not sure the difference).

I suppose it makes sort of sense. You are passing a string of numbers and expecting the server to understand. Servers in different timezones and language settings need to understand what to do with it.

Thanks Scott.

Richard.