Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: Copy a nvarchar field to a date field?

    I have a table with about 3 million records and need to get a field into a YYMMDD date format.

    Currently the field is a nvarchar data type set to 6 characters.

    I tried to create a new field using the datetime type and update the data from the nvarchar to the datetime field, however it returned null values.

    Fields I currently have are:

    [Extract Date] = nvarchar(6)
    [Format Date] = datetime

    Any ideas on how I can get the nvarchar into a YYMMDD date format?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just to clarify -- you want to convert your NVARCHAR(6) values into a DATETIME column?

    because DATETIME columns aren't actually stored in YYMMDD format, they are stored in internal datetime format

    what format is the data in the NVARCHAR(6)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    Sorry kind of new to using SQL Server, not sure all of my terminology was correct.

    The [Format Date] field doesn't have to be datetime format, it just needs to be in a YY/MM/DD format.

    Currently I have the [Extract Date] field formatted as nvarchar(6). The [Extract Date] field came right out of a flat file so the only way I could pull it into SQL Server was as nvarchar and I can't find a good way to get it into a YY/MM/DD format.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slw2206 View Post
    Currently I have the [Extract Date] field formatted as nvarchar(6).
    but what format is it? YYMMDD? MMDDYY? DDMMYY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    5
    its also in YYMMDD format.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slw2206 View Post
    its also in YYMMDD format.
    o rly?

    SELECT STUFF(STUFF([Extract Date],5,0,'/'),3,0,'/') AS [Format Date]
    FROM ...

    you didn't actually need to add that second column to the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •