Date Conversion 2

[meldrape]

Hello there,

I have a text file with dates in yymmdd format, which I have managed to convert to mm/dd/yy with this:

select CAST ( ENDDATE AS datetime) as ENDDATE from JobImportHeader

but I really need yyyy. Is there a way to convert yy to yyyy? Thanks in advance!

 

[organicg]

I've never done this, but is says it can by done with CONVERT function in SQL BOL. Here's some of what it says:

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
(table is in SQL BOL)

 

[meldrape]

Thanks, Organicglenn. I did try that in the beginning, but it did not work. CAST worked much better. I can live with the two digit year. Thanks for your input!

 

[clarkin]

CAST and CONVERT do exactly the same thing, they are different wording for the same command.

Your CAST ( ENDDATE AS datetime) is the same as doing CONVERT(datetime, ENDDATE).

What you want to look at is the [style] option mentioned by organicglenn. See here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

or just use CONVERT(datetime, ENDDATE, 101)...

The other thing to remember is, if you have them in a database column of type datetime - then you can display them in whatever format you like - just use convert and the styles (eg CONVERT(varchar, myDatetimeColumn, 101) )

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[meldrape]

I changed the data type in the column from varchar to datetime, and used your second suggestion and it worked. I was afraid it would screw something up, but it worked famously. Many thanks again to both of you for your help.