CODEWITHSUNDEEP
t-sqldateformat
user1238784
user1238784

Reputation: 2440

T-SQL using date format accounting the format of sql server

I have the following query:

Select dateadd(HOUR, 24, '2018/10/15 00:00:00')

This works on my local SQL Server, but fails on SQL-Express due to the time format, which is the italian date format. So I should write:

Select dateadd(HOUR, 24, '15/10/2018 00:00:00')

How to account for the difference in date format of the two servers. I would like my query to run on both. Which syntax should I use?

Upvotes: 0

Views: 103

Answers (2)

LittleSweetSeas
LittleSweetSeas

Reputation: 7054

I suggest you another approach, that may be helpful whenever your chances to standardize the input are limited: beside any specific culture on SQL Server, you can set a command-wide date format that applies only to subsequent commands, using SET DATEFORMAT.

Eg.

SET DATEFORMAT ymd; 
Select dateadd(HOUR, 24, '2018/10/15 00:00:00')

SET DATEFORMAT dmy; 
Select dateadd(HOUR, 24, '15/10/2018 00:00:00')

You can use different formats by switching y (year), m (month) and d (day) characters.

Reference: SET DATEFORMAT

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

When using string literals as date, time, datetime or datetime2 values, always use ISO8601 format, since SQL Server will always convert it correctly to the appropriate data type.

  • For date only, use yyyy-mm-dd (2018-10-15)
  • For time only, use hh:mm:ss (24 hours) (17:33:25)
  • For Date + time, use yyyy-mm-ddThh:mm:ss again, (24 hours) (2018-10-15T17:33:25)

Upvotes: 2

Related Questions