Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marytaydavis
Starting Member
1 Post |
 Posted - 2015-02-04 : 12:23:13
|
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert(datetime,convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:Error: SQL0204 - CONVERT in *LIBL type *N not found.I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?Any suggestions? I appreciate your help!  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-04 : 12:30:59
|
| Looks like you aren't using Microsoft SQL Server, which is what SQLTeam is for. I would recommend posting your question on a site that specializes in the technology you are using.But to answer your question in regards to MSSQL, this works: declare @d1 decimal(10,0), @d2 datetimeset @d1 = '20140503'select @d1set @d2 = convert(datetime, convert(char(8), @d1, 112))select @d2Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-02-04 : 17:02:16
|
| Looks like DB2. If so, try this:date(substr(QHSTDT,1,4)||'-'||substr(QHSTDT,5,2)||'-'||substr(QHSTDT,7,2)) |
|
|
|
|
|
|
|
|
Converting a Decimal date value to Date field