locked
get current date data and day part from column RRS feed

  • Question

  • hello Friends

    I have a table in sQL that has a column

    Entry date

    02-01-2013 12:01:13

    and its datatype is nvarchar(19)

    now I need to fetch date portion in it and month and year.

    something like ;

    select day(entry_date),month(entry_date),year(entry_date)
    FROM vicidial_list

    but when i do this

    i get this error

    Msg 8115, Level 16, State 2, Line 2
    Arithmetic overflow error converting expression to data type datetime.
    .

    even i can not fire Query like .

    select *
    FROM vicidial_list where entry_date =getdate()

    It gives me same error]

    Kindly help me .

    where i'm going wrong

    Friday, January 25, 2013 12:23 PM

All replies

  • pls try

    SELECT '02-01-2013 12:01:13' entrydate INTO #temp
    
     select day(CAST(entrydate AS DATETIME)),month(CAST(entrydate AS DATETIME)),year(CAST(entrydate AS DATETIME))
     FROM #temp

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, January 25, 2013 12:29 PM
  • chk this

    Declare @Table Table
    (
    Entrydate Nvarchar(19)
    )

    Insert @Table
    Select '02-01-2013 12:01:13'

    Select Day(ActualDate),Month(ActualDate),Year(ActualDate)From (Select *,Convert(Datetime,EntryDate)As ActualDate From @Table)Dd
    Select * From @Table
    Where Convert(Datetime,EntryDate) = Getdate();


    Please have look on the comment

    Friday, January 25, 2013 12:29 PM
  • The error should be beacuse you may have worng data in your table.

    Please try the below:

    Create table T1(Dat nvarchar(19)) Insert into t1 Select '02-01-2013 12:01:13' Insert into t1 Select '22-01-2013 12:01:13' Select DAY(Dat),MONTH(Dat),YEAR(Dat) from T1 Where ISDATE(Dat) =1 Drop table T1



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, January 25, 2013 12:48 PM
  • The data in your table might not be correct. As Lateesh said, please check your data and proceed further.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Friday, January 25, 2013 1:37 PM
  • Create table #T(Dates nvarchar(19))
    Insert into #t Select '02-01-2013 12:01:13'
    Insert into #t Select '22-01-2013 12:01:13'
    select DAY(convert(date , dates,105)) as [Day], Month(convert(date , dates,105)) as [Month],
     year(convert(date , dates,105)) as [Year] from #t
     
    -- Or 
    Select DAY(Dates),MONTH(Dates),YEAR(Dates) from #t Where ISDATE(Dates) =1

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .



    • Edited by Dia.Agha Friday, January 25, 2013 1:59 PM
    • Proposed as answer by ETL_SQL Sunday, January 27, 2013 3:21 AM
    Friday, January 25, 2013 1:47 PM
  • Don't store date information in nvarchar(19) columns as you don't know what garbage may go into such column.

    Check

    Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates. Hopefully this article is strong enough to convince you to never use varchar for dates.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, January 27, 2013 2:00 AM
  • when i tried to change datatype in SQL it gives me error .

    What i do i truncate table then change datatype to datetime then execute SSIS Package.

    Now the Probelm is day value enter in month an dmonth value enter in day part.. kindly suggest me the solution../

    • Edited by BI_Support Monday, January 28, 2013 7:14 AM
    Monday, January 28, 2013 6:23 AM
  • How exactly you're passing dates? Check SET DATEFORMAT command in BOL to set format either to DMY or MDY depending on your data.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, January 28, 2013 3:50 PM