Home > Functions and DMVs, SQL Queries, SQL Server, Stored Procedures, T-SQL > Resolving Datetime datatype Issues during insert/select

Resolving Datetime datatype Issues during insert/select

The developers who work on sql servers, located across the globe, face various issues related to datetime formats used.

Some Queries developed on a particular server may not work on the other as the datetime format used on the second server may be different. This usually gets noticed when the queries/SPs moves into production. These Issues can be resolved by using common datetime format supported for all collation/date format.

The ISO standard formats runs on all the date formats. The formats are as given below.

YYYY-MM-DDThr:mm:ss.mmm     example :  ‘2009-07-31T13:25:37.620’

or

YYYYMMDD hr:mm:ss.mmm   example :  ‘20041022 21:03:30.000’

To select the records from tables you can use the below query irrespective of the Date format.

SELECT * FROM TABLENAME
WHERE DATETIMEFIELD = ‘2009-07-31T13:25:37.620’

SELECT * FROM TABLENAME
WHERE DATETIMEFIELD = ‘20041022 21:03:30.000’

you can type cast by using

SELECT CONVERT(NVARCHAR(25),GETDATE(),112)

SELECT CONVERT(NVARCHAR(25),GETDATE(),126)

so from now on, try using these formats as much as possible and add these to your best practices list. :)

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: