0

Can someone please explain why CreatedDate column even has '2017-07-28 21:36:29.120' value, it gives error when using in where clause with same format which table has like below?

This give error. But this is same with data in table.

where CreatedDate <= '2017-07-28 21:36:29.120'

This is working. But this is not same format with table.

where CreatedDate <= '2017/28/07 21:36:29.120'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Can Aslan
  • 53
  • 2
  • 11
  • 1
    PSA: [ISO date format](https://xkcd.com/1179/). What database? (`tsql` is not specific.) What datatype is `CreatedDate`? What _error_? What [DATEFORMAT](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql)? – HABO Jul 28 '17 at 19:30
  • Datatype is datetime. It is working on my db just not working on source(client) db. `The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.` – Can Aslan Jul 28 '17 at 20:01

1 Answers1

2

The difference is the date format, which is YMD in one case and YDM in the second.

You should never write a date literal in a culture depending format!

This works on your machine, but will break on a customer's machine with different culture settings.

Find some details about implicit (language bound) settings here

SELECT * FROM sys.syslanguages;

You can use 

SET DATEFORMAT xyz --details here: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql

Or 

SET LANGUAGE xyz --Find supported languages with the statement above

But best - and the only recommended! - is:

Use culture independant formats like ODBCor ISO8601!

Read this related answer and one more related answer.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • This is working : `SET DATEFORMAT dmy; DECLARE @datevar datetime2 = '2017-07-27 15:54:40.000' select top 1 * from TableName(NOLOCK) where CreatedDate <= @datevar`. Also wıthout SET DATEFORMAT dmy it is again working. `DECLARE @datevar datetime2 = '2017-07-27 15:54:40.000 select top 1 * from TableName(NOLOCK) where CreatedDate <= @datevar`. And I forgot to say in question. It is working when send query by SSIS just not working when directly run query in SQL. Not understand why, but your answer is working. Thanks – Can Aslan Jul 29 '17 at 15:47