CODEWITHSUNDEEP
sqlsql-server
cosmarchy
cosmarchy

Reputation: 686

SQL Query with Dates

Here is the structure of my DB:

CREATE TABLE [dbo].[P](
    [O] [nchar](50) NULL,
    [C] [datetime] NULL,
    [S] [nchar](50) NULL,
    [M] [datetime] NULL
) ON [PRIMARY]

GO

I'm really not getting this DateTime malarky with SQL :(

Whenever I run this:

INSERT INTO P (O, C, S, M) 
VALUES ('32','17/04/2014 16:50:37','Complete','21/08/2006 22:50:41')

All I get is this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I have tried cast as datetime and convert using various styles but every time I get the same error.

Can someone please tell me where I am going wrong?

Thanks

Upvotes: 0

Views: 73

Answers (2)

marc_s
marc_s

Reputation: 754258

There are many string formats for a date supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

So in your concrete case, try to use this INSERT: 

INSERT INTO P (O, C, S, M) 
VALUES ('32', '2014-04-17T16:50:37', 'Complete', '2006-08-21T22:50:41')

(with dashes, and with a "T" delimiter between date and time)

or 

INSERT INTO P (O, C, S, M) 
VALUES ('32', '20140417 16:50:37', 'Complete', '20060821 22:50:41')

(no dashes, no "T" delimiter between date and time)

Upvotes: 2

dodexahedron
dodexahedron

Reputation: 4657

By default, SQL server prefers the YYYYMMDD format.

You can override the format to make your query work without using the convert function by doing the following:

SET DATEFORMAT DMY

Note that this setting does not affect the underlying storage format, and only changes the way dates are interpreted as input, by default, for the context of the current connection.

This will work:

CREATE TABLE #test ( D DATETIME )

SET DATEFORMAT DMY

INSERT  INTO #test
        ( D )
VALUES  ( '21/12/2001 18:05:56' )

DROP TABLE #test

Upvotes: 0

Related Questions