T-SQL or Access ?? question ??

[pangx623]

Hello everyone,
I'm trying to convert data type '20021001' to be recognized as a date.

The problem is that the data is loaded as char(8) into the tables and the
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) will not work for me.

any thoughts/ideas?

 

[boblarson]

Are you trying to convert in Access to go into SQL Server? Or, are you trying to convert from SQL Server to Access?

 

[pdx_man]

Well keeping it in SQL would look something like this:

declare @Var char(8)
select @var = '20021001'

select substring(@Var,1,4) + '/' + substring(@Var,5,2) + '/' + substring(@Var,7,2)
select convert(smalldatetime, substring(@Var,5,2) + '/' + right(@Var,2) + '/' + left(@Var,4))

Similar logic in Access using the MID, LEFT & RIGHT functions ...

 

[RoyVidar]

Isn't yyyymmdd recogized by SQL server?

SELECT Cast('20021001' AS DATETIME)
SELECT Convert(DATETIME, '20021001')

 

[pdx_man]

It would depend on the regional settings. The way I have it will work on any system regardless of the settings.

 

[RoyVidar]

I thought the unseparated YYYYMMDD format, was just another version of ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601), and independent of language settings, as noted in Tibor Karaszi's pages

http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput

Silly me?

 

[pdx_man]

It is a valid format. Continue to read the article you posted. These default settings can be different and altered. Not all dates may be entered in that format. Think of some web based science fiction game set in a different time period or perhaps where the character can time travel. SQL Server is used to store game data. '20061218' on your machine would be December 18, 2006 with the Server Settings of Date Format of YYYYMMDD. If you soley relied on the default system settings, you could get into trouble if your site needed to be moved to another host where the settings are different. This same string could be recognized as as June 20, 1218. While in our game, that may be a valid date and the application would not error, but another application that had validation checks may break.

That being said, don't rely on default settings for implicit conversions. These can be different on each server and give unexpected results. If you know your data coming in and know what you want it to look like, explicitly convert. You won't get into trouble that way.

And I know the arguments, "Oh, this will never be on another server ... it is just a temporary app ...".

Yeah, right.

 

[RoyVidar]

When I read this

"The SET DATEFORMAT session setting does not apply to all-numeric date entries (numeric entries without separators). Six- or eight-digit strings are always interpreted as ymd. The month and day must always be two digits."

http://msdn2.microsoft.com/en-us/library/ms187085.aspx
http://msdn2.microsoft.com/en-us/library/aa224042(sql.80).aspx

I interpreted it as SQL server will always interpret such strings as ymd, regardless of Date Settings, as the Karaszi pages also indicated (language neutral, not SET DATEFORMAT dependent, not SET LANGUAGE depndent).

Well, perhaps I'll have to reconsider, then.

 

[pangx623]

Isn't yyyymmdd recogized by SQL server?

SELECT Cast('20021001' AS DATETIME)
SELECT Convert(DATETIME, '20021001')

This is exactly what I was looking for.

Thanks to everyone for all the great info.