Best way to handle date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Best way to handle date

Author

Topic

SimonG
Starting Member

15 Posts

Posted - 2010-02-18 : 07:12:34

Hi,

I've got a dataset with dates stored in a smalldatetime field - If I run a select statement against this with a where clause on the date I can only return values if I use US date format. Entering a UK date returns an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.'

I need to programatically create a query outside of SQL server but the date passed will be in UK format - how can I get SQL to return UK dates?

Regards,

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 07:15:19

you need to set dateformat using below query

SET DATEFORMAT dmy

to make it accept dates in UK format

But if possible try to pass dates in universal format ccyymmdd rather than changing server format setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 07:39:51

quote:

I need to programatically create a query outside of SQL server but the date passed will be in UK format - how can I get SQL to return UK dates?

Why do it that way? If possible you should store the desired date in the relevent DATE / DATETIME variable for the programming language you are using and then pass that as a parameter to the db call. That way you don't have to worry about formatting the date for SQL -- your interface will take care of it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SimonG
Starting Member

15 Posts

Posted - 2010-02-18 : 07:40:26

Thanks for the heads up - I've left sql alone and used vba to changed the date format passed to the query as per your suggestion.

Regards,

Simon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 07:44:56

ok great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-18 : 07:46:13

Also you should always assign date varaibles with the formation of YYYYMMDD HH:MM:SS in order to wotk with all date settings

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 07:59:17

Additionally you can:

Pass as a Parameter to your query using the native date/time datatype in your application language

Or pass as a string and explicitly convert it to DATETIME:


SELECT CONVERT(datetime, '18/02/2010', 103)

note that this works irrespective of the current locale setting on the server - so even will work even if we force US date formats:


SET DATEFORMAT mdy

SELECT CONVERT(datetime, '18/02/2010', 103)
GO

or as Visakh says format the string-date as '20100218' - an 8 digit date like this is always treated as being ccyymmdd - whatever locale / convert option you are using:


SET DATEFORMAT mdy

SELECT CONVERT(datetime, '20100218', 101) --  "101" is USA mm/dd/yyyy format ...
-- Implicit conversion is safe too:
SELECT DATEADD(Day, 10, '20100218')

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 08:00:50

"store the desired date in the relevent DATE / DATETIME variable for the programming language you are using"

VBScript? Native datetime datetype? Sadly no

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 08:23:14

thankfully I've not been exposed to much VBScript. Java and python mostly for me.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 08:42:45

'70s Basic + ISAM
'80s C + ISAM
'90s C++ + SQL
2000 - Web - ASP - VBScript .... backwards step

...

... but the good news was that SQL had gone from 6.0 through 6,5, 7, 2000 and up to 2008 over that time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources