DATEFORMAT FUNCTION IN SQL

Question

I am new to sql, I am in trouble with date formats I know I can use cast or convert function but I was looking to dateformat (we use format dd/mm/yy). I looks quite interesting function and very easy but there is a problem. IF I run this dataview

SELECT     hotel, id, codicecliente, prodotto, CAST(REPLACE(numeros, ',', '.') AS decimal(7, 2)) AS numero, CAST(REPLACE(importos, ',', '.') AS decimal(7, 2)) AS importo, 
                      CAST(datas AS datetime) AS data, puntovendita, DATEDIFF(day, '01/01/2000', datas) AS datav, isdate(datas) AS Expr1
FROM         dbo.addebititmp
WHERE     (isdate(datas) = 1)

it shows me about 15.000 records it is not showing all record with data with day more than 12 because system recognizes first 2 as month not day if I add SET DATEFORMAT dmy and run the following

SET DATEFORMAT dmy

SELECT     hotel, id, codicecliente, prodotto, CAST(REPLACE(numeros, ',', '.') AS decimal(7, 2)) AS numero, CAST(REPLACE(importos, ',', '.') AS decimal(7, 2)) AS importo, 
                      CAST(datas AS datetime) AS data, puntovendita, DATEDIFF(day, '01/01/2000', datas) AS datav, isdate(datas) AS Expr1
FROM         dbo.addebititmp
WHERE     (isdate(datas) = 1)

It shows correctly all 38.000 records but when I save the view system shows error: incorrect syntax near the keyword set. So I can run but not use it. I tried also with transact sql but if I creat a view with dateformat it does not save the dateformat and shows less records. Any suggestion? Thank You

Use `'yyyymmdd'` (e.g. '20141122'). It works the same regardless of dateformat setting. — adrianm, Nov 07 '14 at 12:45
To summarize your question. You want to use ISDATE() in WHERE class for date format (dd/mm/yy). But you was unable to do it. Also you don't want to use convert inside ISDATE(). Because it may throw unwanted exception while converting non date values and SELECT query may fail. Am I correct? — Veera, Nov 07 '14 at 14:07
If you're on SQL Server 2012 or 2014, you can use [`TRY_CONVERT()`](http://msdn.microsoft.com/en-us/library/hh230993(v=sql.110).aspx) instead of `ISDATE()` or `TRY_PARSE()` with a defined language that uses `dd/mm/yy` format. — Bacon Bits, Nov 07 '14 at 14:34
Yes Veera you're correct, if I use isdate in where class using date format dd/mm/yy I lose data so I should convert before but if I convert I get an exception if I use try_convert as Bacon suggests I lose data because if data is 31/12/2014 I lose it, why dateformat can't be saved in a view? This would solve easily, It's weird I can execute but not save. Thank You — lucullo08, Nov 07 '14 at 15:58

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

It looks like you are probably Italian?? If so, you should change your default language to Italian on the server. This will also by default change your DATEFORMAT to accept European-style dates. I am assuming all your char-formatted dates will be stored in that format? This will show you how:

How to change default language for SQL Server?

Also, regarding saving the dateformat setting in the view, you can't save it in a view for the same reason you can't save "set nocount on" in a view. But you can set the dateformat in a stored proc that references the view. But I really think in your case you should set the server-wide language, which will address the issue.

edited May 23 '17 at 10:25

Community

1
1

answered Nov 07 '14 at 16:03

Kevin Suchlicki

3,096
2
15
17

Changing default language does not fix it and the problem is I should rewrite many queries which work on english format on this server (for number conversion for istance). Store procedure is a good idea but I should rewrite vb.net to get data from datareader any idea for the view? – lucullo08 Nov 07 '14 at 17:52
Try this: SET LANGUAGE Italian; SELECT ISDATE('15/2/2014'); You will see that SQL Server now sees it as a valid date. Now you just have to set the Language setting server-wide (you should research it more if you are having problems). It will work. Try reading this: http://support.microsoft.com/kb/173907 – Kevin Suchlicki Nov 07 '14 at 21:58

score 0 · Answer 2 · answered Nov 08 '14 at 16:27

Kevin your solution is not working for me. Solved this way Create this function and use as I wish on view I suggest to leave it as nvarchar instead of date to control all data in the view.

-- ============================================= CREATE FUNCTION fdataitaineng ( -- Questa funzione trasforma da formato italiano ad inglese @datas nvarchar (12) ) RETURNS nvarchar (12) AS BEGIN

DECLARE @datan as nvarchar(12)

select @datan=SUBSTRING(@datas, 4, 2) + '/' + SUBSTRING(@datas, 1, 2) + '/' + SUBSTRING(@datas, 7, 4)

RETURN @datan

END GO

Please do not use a T-SQL User-Defined Function for this. It is horribly inefficient and unnecessary since a built-in function already exists that does this (and more). Please see my answer. — Solomon Rutzky, Nov 08 '14 at 17:06

Solomon Rutzky · Accepted Answer · 2014-11-08T17:05:04.000

The error when creating the view with SET DATEFORMAT dmy is due to a view being an encapsulation of a single query, not multiple commands. If you need multiple commands, then you would have to use a Multistatement Table-Valued Function. But using a TVF is not necessary here.

Use TRY_CONVERT as it will handle both the translation and the "ISDATE" behavior. It will either convert to a proper DATETIME or it will return NULL. In this sense, a non-NULL value equates to ISDATE returning 1 while a NULL value equates to ISDATE returning 0. Since your data is in the format of DD/MM/YYYY, that is the "style" number 103 (full list of Date and Time styles found on the CAST and CONVERT MSDN page).

SELECT TRY_CONVERT(DATETIME, tmp.DateDDMMYYYY, 103) AS [ConvertedDate],
       tmp.ShouldItWork
FROM (
   VALUES('23/05/2014', 'yes'),
         ('05/23/2014', 'no'),
         ('0a/4f/2014', 'no')
 ) tmp(DateDDMMYYYY, ShouldItWork);

Results:

ConvertedDate            ShouldItWork
2014-05-23 00:00:00.000  yes
NULL                     no
NULL                     no

DATEFORMAT FUNCTION IN SQL

3 Answers3