Post reply

problem with importing date&time together in the same column from excel 2003 to SQL server 2008

  • March 7, 2012 at 8:55 pm

    #252726

    Hi, I started SQL server 2008 yesterday.

    I don't know how to display the date and time together in the same column.

    I got an .xls excel worksheet containing a column of date and time together like '02/03/2012 07:33:45' in a format 'DD/MM/YYYY HH24:MI:SS'.

    The result I want is, '02/03/2012 07:00:00', and summation of all IDs that the time records are in the rank of 07:00:00 to 07:59:99.

    the result should be:

    date 2/3/2012 time 7am sum id = 3

    date 2/3/2012 time 8am sum id = 5

    date 2/3/2012 time 9am sum id = 6 ...so on.

    My problem is after I reviewed the imported data using select* from

    , there's only DATE shown in my date&time column. I want the DATE with TIME in HH:MM what ever just want to fid the way to display them together.

    I've tried analyzed the problem with SQL*Plus.

    In SQL*Plus case, I have to >> alter session NLS_DATE_FORMAT 'DD-MM-YYYY HH24:MI:SS'.

    Does any one have any idea with this stupid problem?

    How can I alter sesstion nls_date_format in T-SQL?

    I tried convert/cast etc but didnt work.

    ps; getdate()'s result is ok, (2012/03/08 12:22:37...)

    ps2: I'm not a native English speaker, sorry for my poor english.

  • March 8, 2012 at 1:56 pm

    #1456807

    How did you do the import exactly?

    What is the datatype of the column that stored the date and time information?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 8, 2012 at 6:46 pm

    #1456915

    Look at the CONVERT function in the documentation and various formats, and then take a SUBSTRING after conversion to varchar.

    Other approach would be without conversion to varchar, staying in the datetime (my favorite):

    SELECT SomeTime = GETDATE(),

    TruncatedToHour = dateadd( hour, datediff(hour,0,GETDATE()), 0 )

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • March 9, 2012 at 11:24 am

    #1457319

    I'd do the import again and this time handle the formatting pre-import when you select the xls source. If you use the import/export wizard this can be done quite easily.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply