none
Date Time Error.

    Question

  • Hi friends,

    I am having one table which is having startdate and enddate for one work. Now enddate might be null because work is continue.

    When i try to convert it like below it gives me error. The startdate and endate in the table is varchar datatype.

    select convert(datetime, startdate) from table

    select convert(datetime, enddate) from table
    where enddate is not null

    the startdate works ok but enddate gives problem and says:
    Syntax error converting datetime from character string.

    Any suggestion please?

    thanks.

    Monday, September 15, 2008 11:36 AM

Answers

  • Hi,

    while converting to datetime from string you have to be careful with format.

    The dates in varchar are in which format? yyyymmdd, mmddyyyy or somthing else?

    according to that you need to provide the style for converting

    select convert(datetime, enddate,<style>) from table.

     

    and that style you can decide from here.

    First 2 columns gives you the style number.

    Without century (yy) (1) With century (yyyy) Standard Input/Output (3)

    -

    0 or 100 (1, 2)

    Default

    mon dd yyyy hh:miAM (or PM)

    1

    101

    U.S.

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    British/French

    dd/mm/yy

    4

    104

    German

    dd.mm.yy

    5

    105

    Italian

    dd-mm-yy

    6

    106 (1)

    -

    dd mon yy

    7

    107 (1)

    -

    Mon dd, yy

    8

    108

    -

    hh:mmTongue Tieds

    -

    9 or 109 (1, 2)

    Default + milliseconds

    mon dd yyyy hh:miTongue Tieds:mmmAM (or PM)

    10

    110

    USA

    mm-dd-yy

    11

    111

    JAPAN

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    -

    13 or 113 (1, 2)

    Europe default + milliseconds

    dd mon yyyy hh:mmTongue Tieds:mmm(24h)

    14

    114

    -

    hh:miTongue Tieds:mmm(24h)

    -

    20 or 120 (2)

    ODBC canonical

    yyyy-mm-dd hh:miTongue Tieds(24h)

    -

    21 or 121 (2)

    ODBC canonical (with milliseconds)

    yyyy-mm-dd hh:miTongue Tieds.mmm(24h)

    -

    126 (4)

    ISO8601

    yyyy-mm-ddThh:mmTongue Tieds.mmm (no spaces)

    127(6)

    ISO8601 with time zone Z.

    yyyy-mm-ddThh:mmTongue Tieds.mmmZ

    (no spaces)

    -

    130 (1, 2)

    Hijri (5)

    dd mon yyyy hh:miTongue Tieds:mmmAM

    -

    131 (2)

    Hijri (5)

    dd/mm/yy hh:miTongue Tieds:mmmAM


     

    Monday, September 15, 2008 11:49 AM
  • Hi Mangal,

     

    I have found invalid record into enddate field. There were millions of record so it wasn't obvious to see invalid record. But it was working for startdate and not working for enddate. So i have tried to check for some different data. I have found some record with invalid data. It has been fixed now.

     

    Thanks for your help.

    Monday, September 15, 2008 12:23 PM

All replies

  • Hi,

    while converting to datetime from string you have to be careful with format.

    The dates in varchar are in which format? yyyymmdd, mmddyyyy or somthing else?

    according to that you need to provide the style for converting

    select convert(datetime, enddate,<style>) from table.

     

    and that style you can decide from here.

    First 2 columns gives you the style number.

    Without century (yy) (1) With century (yyyy) Standard Input/Output (3)

    -

    0 or 100 (1, 2)

    Default

    mon dd yyyy hh:miAM (or PM)

    1

    101

    U.S.

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    British/French

    dd/mm/yy

    4

    104

    German

    dd.mm.yy

    5

    105

    Italian

    dd-mm-yy

    6

    106 (1)

    -

    dd mon yy

    7

    107 (1)

    -

    Mon dd, yy

    8

    108

    -

    hh:mmTongue Tieds

    -

    9 or 109 (1, 2)

    Default + milliseconds

    mon dd yyyy hh:miTongue Tieds:mmmAM (or PM)

    10

    110

    USA

    mm-dd-yy

    11

    111

    JAPAN

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    -

    13 or 113 (1, 2)

    Europe default + milliseconds

    dd mon yyyy hh:mmTongue Tieds:mmm(24h)

    14

    114

    -

    hh:miTongue Tieds:mmm(24h)

    -

    20 or 120 (2)

    ODBC canonical

    yyyy-mm-dd hh:miTongue Tieds(24h)

    -

    21 or 121 (2)

    ODBC canonical (with milliseconds)

    yyyy-mm-dd hh:miTongue Tieds.mmm(24h)

    -

    126 (4)

    ISO8601

    yyyy-mm-ddThh:mmTongue Tieds.mmm (no spaces)

    127(6)

    ISO8601 with time zone Z.

    yyyy-mm-ddThh:mmTongue Tieds.mmmZ

    (no spaces)

    -

    130 (1, 2)

    Hijri (5)

    dd mon yyyy hh:miTongue Tieds:mmmAM

    -

    131 (2)

    Hijri (5)

    dd/mm/yy hh:miTongue Tieds:mmmAM


     

    Monday, September 15, 2008 11:49 AM
  • Hi Mangal,

     

    I have found invalid record into enddate field. There were millions of record so it wasn't obvious to see invalid record. But it was working for startdate and not working for enddate. So i have tried to check for some different data. I have found some record with invalid data. It has been fixed now.

     

    Thanks for your help.

    Monday, September 15, 2008 12:23 PM