Re: Number to Date

From: Steve Kass (skass_at_drew.edu)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 01:37:35 -0400

Miguel,

  Anith's solution will work for dates in or after the year 2000, but it
could give the wrong answer for earlier dates, if they are stored as
integers or as strings but without a leading zero. Here's an alternate
solution that should work:

select dateadd(year,@c/1000,-1) + @c%1000 from yourTable

Steve Kass
Drew University

Miguel Salles wrote:

>Hi,
>
>I have a legacy system that uses a strange date format, wich I'd like to
>convert for datetime on a new table.
>The date is in this format: 104001(2004-01-01), 104002(2004-01-02),
>104271(2004-09-27)
>I think that I'll have to break the problem in two parts:
>The year - three first digits + 1900
>the day - three last digits (dy) day of the year.
>I can generate the days by using "datepart(dy,date)", but how can i make the
>way back? (to transform 271 in 09-27)
>
>Thanks for your help.
>
>
>

Relevant Pages

  • Number to Date
    ... I have a legacy system that uses a strange date format, wich I'd like to ... convert for datetime on a new table. ... The year - three first digits + 1900 ...
    (microsoft.public.sqlserver.mseq)
  • Re: Dates
    ... I have never ever had any problems with a date in ISO 8601 format. ... The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ... System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +260 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: How to Validate a Date Filed.
    ... I'm writing, and the fourth test is just getting fancy; ... IsValid As Boolean = True) As DateTime ... > You can validate a date using Regular Expressions. ... > objects and setting culture to a country that uses the date format you ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Boolean field in dataset
    ... Crystal only formats the datetime data in the ... dataset extracted from sql database but not the datetime variable data that I ... it recognized it to be datetime field ... When I format these 2 datetime fields Crystal ...
    (microsoft.public.vb.crystal)
  • Re: Date/Time. Looked Everywhere. Urgent. Thanks.
    ... to parse a date specified in the US English format: ... DateTime dtDate = DateTime.Parse(strDate, ifProvider); ... > Dim news As New XmlDocument ... I would use a DateTime field not only just ...
    (microsoft.public.dotnet.framework.aspnet)