September 1, 2015 at 1:47 am
HI,
I have a date field stored in a character type field ( this field is a spare one in an ERP package )
I am trying to validate and convert to a date format using the following;
The results are ;
Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format
What do I need to do to force this to identify as dd/mm/yy date format ?
Thanks in advance
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 1, 2015 at 2:00 am
See :
http://www.karaszi.com/sqlserver/info_datetime.asp
The ultimate guide to the datetime datatypes.
This explains the datetime datatypes very extensively. Far better than I could anwser your question.
Ben
September 1, 2015 at 2:08 am
Hi Thanks for the reply -
The simple answer is -
SET LANGUAGE British
GO
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 1, 2015 at 2:28 am
Ahh right - stumbling block number 2..
You cannot use SET LANGUAGE British within a view !
Looks like the database needs to be set as dmy ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 1, 2015 at 5:27 am
It is possible to set Language for a connection.
Or you can also set Language for an account.
But from experience I am familliar with a lot of problems when the format is different for different people/connections/account/applications.
I have some favorite formats (YYYYMMDD HH:MM:SS.sss or YYYYMMDDTHH:MM:SS.sss), because this is ('almost') setting independend.
The effects of the settings (connection/account/database/server/client), is in my experience always complex and it's easy to forget something, there are to many places with the regional settings and to many different variations. This becomes worse when importing from something like Excel or CSV files.
Ben
September 1, 2015 at 7:39 am
Another option is to change only the dateformat, or the condition (which can be painful to get it completely right).
SET dateformat dmy;
CREATE TABLE #TestDates(
UserField1 char(8));
INSERT INTO #TestDates
VALUES
('29/09/08'),
('09/06/15'),
('18/03/13'),
('09/10/14');
SELECT UserField1,
CONVERT(datetime, UserField1, 3),
CASE WHEN isdate( UserField1 )= 1 then --UserField1 LIKE '[0-3][0-9]/[0-1][0-9]/[0-9][0-9]' then
CONVERT(datetime, UserField1, 3)
ELSE
CAST( 0 as Datetime )
END
FROM #TestDates
GO
DROP TABLE #TestDates
September 1, 2015 at 9:54 am
Another possibility,
I hijacked the code from Luis for the generation of the table.
Test for correct date is ommitted. (To keep it simple).
I reformatted the data in such a format that it works for any regional setting.
So it works for American/English/European settings.
Used several fields to show 'some' possibilities.
Notice: It is assumed that the date is in the 21 century.
Having code which works for any setting has a big advantage.
Ben
SET dateformat dmy;
-- exec sp_drop #TestDates -- In my environment drops the table if it exists
CREATE TABLE #TestDates(
UserField1 char(8));
INSERT INTO #TestDates
VALUES
('29/09/08'),
('09/06/15'),
('18/03/13'),
('09/10/14');
-- For input the format YYyymmdd is used.
select
'20'+SUBSTRING(userfield1,7,2)
+SUBSTRING(userfield1,4,2)
+SUBSTRING(userfield1,1,2) A_date_string
, convert(datetime,
'20'+SUBSTRING(userfield1,7,2)
+SUBSTRING(userfield1,4,2)
+SUBSTRING(userfield1,1,2)) A_date_field -- output system dependend.
,
CONVERT(varchar(10), -- Output chopped to 10 chars only
convert(datetime,
'20'+SUBSTRING(userfield1,7,2)
+SUBSTRING(userfield1,4,2)
+SUBSTRING(userfield1,1,2)), 126) -- output 126 is yyyy-mm-ddThh:mm:ss.sss
Double_conversion
,
CONVERT(varchar(50),
convert(datetime,
'20'+SUBSTRING(userfield1,7,2)
+SUBSTRING(userfield1,4,2)
+SUBSTRING(userfield1,1,2)))
Regional_dependend_conversion
,* from #testdates
September 1, 2015 at 10:06 am
When you store dates as character, store them as:
YYYYMMDD
which always works under any/all SQL settings.
For the current data, you put it into that format to check it:
CASE WHEN isdate( '20' + RIGHT(arc.UserField1, 2) + substring(arc.UserField1, 4, 2) + LEFT(arc.UserField1, 2) )= 1 then
CAST( '20' + RIGHT(arc.UserField1, 2) + substring(arc.UserField1, 4, 2) + LEFT(arc.UserField1, 2) as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 1, 2015 at 10:28 am
Best thing, store date and datetime values using the proper date or date/time data type, then you don't have the conversion issues.
September 1, 2015 at 11:24 am
Lynn Pettis (9/1/2015)
Best thing, store date and datetime values using the proper date or date/time data type, then you don't have the conversion issues.
Yes I do agree, however, with legacy systems - sometimes we have to work with the way things are until we can change this to a more appropriate system and structure.
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 3, 2015 at 2:34 am
Lynn Pettis (9/1/2015)
Best thing, store date and datetime values using the proper date or date/time data type, then you don't have the conversion issues.
"Proper date/time data".
Problem is that there is not a single Proper type. There are several types in SQL-server. Even more types within Microsoft software. Datetime in Excel for example.
(Excel can not store 8 o'clock in the morning in an exact format).
Other databases have other formats both proper and not proper.
Most systems support strings, both conversion too and from a string, so between systems often the string format is used to transport date/time data.
For string representation of date/time data, I prefer numeric above 'names', I prefer most significant first. I prefer the 24 hour system. But there are a lot of other preferences in the world.
Ben
September 3, 2015 at 2:50 am
You're confusing dates with date formats. Since this is a SQL Server forum, let's talk about SQL Server, which stores dates internally as a number (two numbers, maybe - the specifics aren't important). Any way you choose to present a date is just a representation of that number, totally independent of the way it's stored. I'd be surprised if there are any RDBMSs to which that doesn't apply. If you need to interact with any systems or applications that don't support that (that may or may not include Excel), then you can convert to a different format when you do your ETL.
John
September 3, 2015 at 3:19 am
John Mitchell-245523 (9/3/2015)
You're confusing dates with date formats. Since this is a SQL Server forum, let's talk about SQL Server, which stores dates internally as a number (two numbers, maybe - the specifics aren't important). Any way you choose to present a date is just a representation of that number, totally independent of the way it's stored. I'd be surprised if there are any RDBMSs to which that doesn't apply. If you need to interact with any systems or applications that don't support that (that may or may not include Excel), then you can convert to a different format when you do your ETL.John
John - the problem was displaying dates from a character field that had been setup in an application, SQL back end - gave the flexibility not to restrict the fields to DATE or CHARACTER types, therefore making it more usable -
However the problem then comes with validating data when inputting - as there was none - so creating a view that presents the date in a dd/mm/yy format was required.
Then finding out that the server had been setup as American format mm/dd/yy - which caused date conversion issues.
The problem has been resolved and I appreciate everybody's input.
Thank you
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 3, 2015 at 3:55 am
Thanks Steve. Glad you got it all sorted. I was actually responding to Ben's point. Sorry, I should have made that clear.
John
September 3, 2015 at 4:13 am
John Mitchell-245523 (9/3/2015)
You're confusing dates with date formats. Since this is a SQL Server forum, let's talk about SQL Server, which stores dates internally as a number (two numbers, maybe - the specifics aren't important). Any way you choose to present a date is just a representation of that number, totally independent of the way it's stored. I'd be surprised if there are any RDBMSs to which that doesn't apply. If you need to interact with any systems or applications that don't support that (that may or may not include Excel), then you can convert to a different format when you do your ETL.John
No I am not confusing storage with representation. There are a 'proper' number of ways to store date's and times even within SQL-server. And although this is a SQL Server forum, SQL Server is not isolated from the rest of the world.
And how dates and times are stored internally is important, because that also describes what can be done and can not be done with dates and times.
The example of Excel was specifically used. Why? In Excel when you store a date/time or a time, you can not store 08:00 exactly. (In SQL server you can).
Comparing a datetime field (I have seen this multiple times) with something like 20090626 23:59:59.999 is a bad idea, why because of the way the data is stored and rounded.
For many problems there is often more than one solution. Although a number of the solutions might be bad, there is often also a number of 'proper' solutions.
For example within the older SQLserver versions there where a number of ways to implement time only. So some solutions used a string to store time. While other used numbers to store time and still others used a datetime format (where the date was set to a specific value or was ignored).
Even for something as simple as a date of birth I can come up with plenty of problems. Where there are more than one 'proper' solution.
Ben
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply