You must Sign In to post a response.
  • Category: SQL Server

    How to validate date in char datatype ?


    Are you looking for a way to validate date in char datatype ? then read this thread to know how to validate it



    I have "from date" datatype in my table as char(10). In this case how can i get the rows which has "from date" >= 12/15/2014. Please give me the query

    Thanks in advance
  • #755460
    In this case you need to use convert function to convert the char datatype to the data type and then perform the required filtering in the where clause as shown below:

    select convert(date,fromdate) from tablename
    where convert(date,fromdate) > convert(date,'12/15/2014')

    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #755462
    You can use this Following URL for Validate date in Char datatype
    msdn.microsoft.com/en-IN/library/ms187347.aspx

  • #755469
    I have seen many times developers use varchar or char datatype for string datetime and then they cant be able to filter dates, Basically for date operation you should use DateTime datatype only.
    With the help of SQL CONVERT method you need to convert it to date
    see below snippet

    Select CONVERT(VARCHAR(10),col1,101) from database1

    it will convert your char to date in MM/dd/yyyy format

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #755487
    Please check this below sample working code for char to date time





    declare @c char(14)
    select @c='20141231122025'

    Select Cast(Substring(@c,1,8) + ' ' + Substring(@c,9,2)+':'+ Substring(@c,11,2)+':'+ Substring(@c,13,2) as DateTime)

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #755494
    hi,

    better u have to convert datetime into character, whatever datetime value u entered..


    ex:

    string date = "01/08/2008";
    DateTime dt = Convert.ToDateTime(date);

    then in sql :

    u have to convert the datetime to varchar using convert function like
    :
    SELECT convert(varchar(30), '10/23/2016', 101)


    thanks,
    chitaranjan

  • #755502
    Hai Kumaresh,
    While comparison for the date, make the conversion at the both side and then do it.
    Both side of the comparison operator, the type should be same and then only you can compare the dates.
    Below is an example which can be used for the date comparison:

    Select * fro <table_name>
    Where CONVERT(VARCHAR(10),FromDate,101) >= CONVERT(VARCHAR(10),'12/15/2014;,101)

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #755514
    I ran this:
    Select top 10 from_date from table
    where CONVERT(VARCHAR(10),from_date,101)>=convert(VARCHAR(10),'12/20/2014',101).
    The output returned reocords of 2006, 2008, 2009.

    Please suggest

  • #761039
    Hi
    Kumaresh

    You can go through this code


    Select * from table11

    Select top 10 from_date from table11
    where CONVERT(VARCHAR(10),from_date,103)>=convert(VARCHAR(10),'12/20/2014',101)


    --Create Table table11
    --(
    --from_date date
    --)

    --Insert into table11 values('10/20/2014')



    also refer this date Format


    Select CONVERT(varchar(12),GetDate(),101)
    Select CONVERT(varchar(12),GetDate(),102)
    Select CONVERT(varchar(12),GetDate(),103)
    Select CONVERT(varchar(12),GetDate(),104)
    Select CONVERT(varchar(12),GetDate(),105)
    Select CONVERT(varchar(12),GetDate(),106)
    Select CONVERT(varchar(12),GetDate(),107)


    I have attached Image given below.

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment


  • Sign In to post your comments