Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: Converting Date to other format

    Hello

    I am using SQL server 2008 R2. I have a query where I have dates in different columns pulled from my database among other things. The client is requiring that the date be formatted as mmddyyyy. The database returns the date value as varchar (8) and formatted as yyyymmdd. I have tried several things to get this to be converted but haven't had any luck. My query is as follows. If someone could let me know the best way to do this it would be very helpful.

    select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city,
    appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, appointments.appt_date, person.date_of_birth,
    person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name, provider_mstr.last_name, specialty_mstr.description,
    appointments.location_id, location_mstr.location_name, person.email_address
    into #TESTFILE
    from ngprod.dbo.appointments
    inner join ngprod.dbo.person
    ON appointments.person_id=person.person_id
    inner join ngprod.dbo.patient
    ON appointments.person_id=patient.person_id
    inner join ngprod.dbo.provider_mstr
    ON appointments.rendering_provider_id=provider_mstr.p rovider_id
    inner join ngprod.dbo.specialty_mstr
    ON provider_mstr.specialty_code_1=specialty_mstr.spec ialty_code
    inner join ngprod.dbo.location_mstr
    ON appointments.location_id=location_mstr.location_id
    where appointments.appt_date ='20130624'
    update #TESTFILE
    set last_name=(last_name+ ' MD')
    alter table #TESTFILE
    add Ending nvarchar (70) not null default ('$')
    --Select CONVERT(nvarchar(8), getdate(),107) as [Date in mmddyyy Format]
    --SELECT convert(varchar (8),appointments.appt_date,'mmddyyyy')
    --select convert(char(8),appt_date,117) from #pressganey
    --alter table #TESTFILE
    --alter column appt_date date
    --REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
    --SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS ----[MMDDYYYY]

    select * from #TESTFILE
    order by last_name

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try to do a test with this script:

    Code:
    declare @MyDate varchar(8);
    set @MyDate = '20130624';
    select SUBSTRING(@MyDate, 5, 4) + SUBSTRING(@MyDate, 1, 4) as MyDatemmddyyyy
    Hope this helps.

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    That works in the results but is not updating the information in my resulting #testfile table. How can I get that to update the results? Perhaps i placed that code too late in my query?

    select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city,
    appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, appointments.appt_date, person.date_of_birth,
    person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name, provider_mstr.last_name, specialty_mstr.description,
    appointments.location_id, location_mstr.location_name, person.email_address
    into #testfile
    from ngprod.dbo.appointments
    inner join ngprod.dbo.person
    ON appointments.person_id=person.person_id
    inner join ngprod.dbo.patient
    ON appointments.person_id=patient.person_id
    inner join ngprod.dbo.provider_mstr
    ON appointments.rendering_provider_id=provider_mstr.p rovider_id
    inner join ngprod.dbo.specialty_mstr
    ON provider_mstr.specialty_code_1=specialty_mstr.spec ialty_code
    inner join ngprod.dbo.location_mstr
    ON appointments.location_id=location_mstr.location_id
    where appointments.appt_date ='20130624'
    update #TESTFILE
    set last_name=(last_name+ ' MD')
    alter table #TESTFILE
    add Ending nvarchar (70) not null default ('$')
    declare @MyDate varchar(8);
    set @MyDate = '20130624';
    select SUBSTRING(@MyDate, 5, 4) + SUBSTRING(@MyDate, 1, 4) as MyDatemmddyyyy
    --Select CONVERT(nvarchar(8), getdate(),107) as [Date in mmddyyy Format]
    --SELECT convert(varchar (8),appointments.appt_date,'mmddyyyy')
    --select convert(char(8),appt_date,117) from #pressganey
    --alter table #TESTFILE
    --alter column appt_date date
    --REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
    --SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]

    SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
    select * from #TESTFILE
    order by last_name

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try to change your query to replace

    Code:
    appointments.appt_date
    by

    Code:
    SUBSTRING(appointments.appt_date, 5, 4) + SUBSTRING(appointments.appt_date, 1, 4)
    Hope this helps.

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    When i replace appointments.appt_date with that code i see the following error. My statement looks like below. I'm sorry our DBA quit and I've been thrown into this project and am stuck on this last part. Thanks again for the help.

    Msg 1038, Level 15, State 5, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.


    select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city,
    appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, SUBSTRING(appointments.appt_date, 5, 4) + SUBSTRING(appointments.appt_date, 1, 4), person.date_of_birth,
    person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name, provider_mstr.last_name, specialty_mstr.description,
    appointments.location_id, location_mstr.location_name, person.email_address
    into #testfile
    from ngprod.dbo.appointments
    inner join ngprod.dbo.person
    ON appointments.person_id=person.person_id
    inner join ngprod.dbo.patient
    ON appointments.person_id=patient.person_id
    inner join ngprod.dbo.provider_mstr
    ON appointments.rendering_provider_id=provider_mstr.p rovider_id
    inner join ngprod.dbo.specialty_mstr
    ON provider_mstr.specialty_code_1=specialty_mstr.spec ialty_code
    inner join ngprod.dbo.location_mstr
    ON appointments.location_id=location_mstr.location_id
    where appointments.appt_date ='20130624'
    update #TESTFILE
    set last_name=(last_name+ ' MD')
    alter table #TESTFILE
    add Ending nvarchar (70) not null default ('$')

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server stores dates and times in a bit-oriented form... The actual data is quite convenient for the server, but meaningless to a human being.

    The SQL Client (SSMS, ADO, etc) can convert the internal date format to a locale-specific form. This is the character based form of the date/time/whatever that the user sees by default. The Convert() function can be used to coerce the internal date into almost any of the presentation forms.

    No matter which presentation form you send to SQL Server, the date will be converted to the internal representation for storage. Nothing will change that, unless you make the mistake of storing the date in its character format.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2012
    Posts
    213
    ringrim, try:

    Code:
    SUBSTRING(appointments.appt_date, 5, 4) + SUBSTRING(appointments.appt_date, 1, 4) as appt_date
    Hope this helps.

  8. #8
    Join Date
    Jun 2013
    Posts
    4
    IMEX that worked perfectly. Thank you so much!!!!!!!

Posting Permissions

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